<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Chapter 16 Alternative Storage Engines</title>
<link rel="stylesheet" href="mvl.css" type="text/css" />
<meta name="generator" content="DocBook XSL Stylesheets + chunker.py v1.9.2" />
<link rel="start" href="index.html" title="{book-title}" />
<link rel="up" href="" title="" />
<link rel="prev" href="innodb-storage-engine.html" title="Chapter 15 The InnoDB Storage Engine" />
<link rel="next" href="replication.html" title="Chapter 17 Replication" />
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF">
<div class="navheader">
<table width="100%" summary="Navigation header">
<tr>
<th colspan="3" align="center">Chapter 16 Alternative Storage Engines</th>
</tr>
<tr>
<td width="20%" align="left"><a accesskey="p" href="innodb-storage-engine.html">Prev</a> </td>
<th width="60%" align="center"></th>
<td width="20%" align="right"> <a accesskey="n" href="replication.html">Next</a></td>
</tr>
</table>
<hr>
</div>
<div class="chapter">
<div class="titlepage">
<div>
<div>
<h1 class="title"><a name="storage-engines"></a>Chapter 16 Alternative Storage Engines</h1>

</div>

</div>

</div>
<div class="toc">
<p><b>Table of Contents</b></p><dl class="toc"><dt><span class="section"><a href="storage-engines.html#storage-engine-setting">16.1 Setting the Storage Engine</a></span></dt><dt><span class="section"><a href="storage-engines.html#myisam-storage-engine">16.2 The MyISAM Storage Engine</a></span></dt><dd><dl><dt><span class="section"><a href="storage-engines.html#myisam-start">16.2.1 MyISAM Startup Options</a></span></dt><dt><span class="section"><a href="storage-engines.html#key-space">16.2.2 Space Needed for Keys</a></span></dt><dt><span class="section"><a href="storage-engines.html#myisam-table-formats">16.2.3 MyISAM Table Storage Formats</a></span></dt><dt><span class="section"><a href="storage-engines.html#myisam-table-problems">16.2.4 MyISAM Table Problems</a></span></dt></dl></dd><dt><span class="section"><a href="storage-engines.html#memory-storage-engine">16.3 The MEMORY Storage Engine</a></span></dt><dt><span class="section"><a href="storage-engines.html#csv-storage-engine">16.4 The CSV Storage Engine</a></span></dt><dd><dl><dt><span class="section"><a href="storage-engines.html#se-csv-repair">16.4.1 Repairing and Checking CSV Tables</a></span></dt><dt><span class="section"><a href="storage-engines.html#se-csv-limitations">16.4.2 CSV Limitations</a></span></dt></dl></dd><dt><span class="section"><a href="storage-engines.html#archive-storage-engine">16.5 The ARCHIVE Storage Engine</a></span></dt><dt><span class="section"><a href="storage-engines.html#blackhole-storage-engine">16.6 The BLACKHOLE Storage Engine</a></span></dt><dt><span class="section"><a href="storage-engines.html#merge-storage-engine">16.7 The MERGE Storage Engine</a></span></dt><dd><dl><dt><span class="section"><a href="storage-engines.html#merge-table-advantages">16.7.1 MERGE Table Advantages and Disadvantages</a></span></dt><dt><span class="section"><a href="storage-engines.html#merge-table-problems">16.7.2 MERGE Table Problems</a></span></dt></dl></dd><dt><span class="section"><a href="storage-engines.html#federated-storage-engine">16.8 The FEDERATED Storage Engine</a></span></dt><dd><dl><dt><span class="section"><a href="storage-engines.html#federated-description">16.8.1 FEDERATED Storage Engine Overview</a></span></dt><dt><span class="section"><a href="storage-engines.html#federated-create">16.8.2 How to Create FEDERATED Tables</a></span></dt><dt><span class="section"><a href="storage-engines.html#federated-usagenotes">16.8.3 FEDERATED Storage Engine Notes and Tips</a></span></dt><dt><span class="section"><a href="storage-engines.html#federated-storage-engine-resources">16.8.4 FEDERATED Storage Engine Resources</a></span></dt></dl></dd><dt><span class="section"><a href="storage-engines.html#example-storage-engine">16.9 The EXAMPLE Storage Engine</a></span></dt><dt><span class="section"><a href="storage-engines.html#storage-engines-other">16.10 Other Storage Engines</a></span></dt><dt><span class="section"><a href="storage-engines.html#pluggable-storage-overview">16.11 Overview of MySQL Storage Engine Architecture</a></span></dt><dd><dl><dt><span class="section"><a href="storage-engines.html#pluggable-storage">16.11.1 Pluggable Storage Engine Architecture</a></span></dt><dt><span class="section"><a href="storage-engines.html#pluggable-storage-common-layer">16.11.2 The Common Database Server Layer</a></span></dt></dl></dd></dl>
</div>
<a class="indexterm" name="idm139899505503616"></a><a class="indexterm" name="idm139899505502160"></a><a class="indexterm" name="idm139899505500672"></a><a class="indexterm" name="idm139899505499184"></a><a class="indexterm" name="idm139899505498112"></a><a class="indexterm" name="idm139899505497040"></a><a class="indexterm" name="idm139899505495968"></a><a class="indexterm" name="idm139899505494896"></a><a class="indexterm" name="idm139899505493824"></a><a class="indexterm" name="idm139899505492752"></a><a class="indexterm" name="idm139899505491680"></a><a class="indexterm" name="idm139899505490608"></a><a class="indexterm" name="idm139899505489536"></a><a class="indexterm" name="idm139899505488448"></a><p>
    Storage engines are MySQL components that handle the SQL operations
    for different table types. <a class="link" href="innodb-storage-engine.html" title="Chapter 15 The InnoDB Storage Engine"><code class="literal">InnoDB</code></a> is
    the default and most general-purpose storage engine, and Oracle
    recommends using it for tables except for specialized use cases.
    (The <a class="link" href="sql-syntax.html#create-table" title="13.1.18 CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a> statement in MySQL
    8.0 creates <code class="literal">InnoDB</code> tables by
    default.)
  </p><p>
    MySQL Server uses a pluggable storage engine architecture that
    enables storage engines to be loaded into and unloaded from a
    running MySQL server.
  </p><p>
    To determine which storage engines your server supports, use the
    <a class="link" href="sql-syntax.html#show-engines" title="13.7.6.16 SHOW ENGINES Syntax"><code class="literal">SHOW ENGINES</code></a> statement. The value in
    the <code class="literal">Support</code> column indicates whether an engine
    can be used. A value of <code class="literal">YES</code>,
    <code class="literal">NO</code>, or <code class="literal">DEFAULT</code> indicates that
    an engine is available, not available, or available and currently
    set as the default storage engine.
  </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SHOW ENGINES\G</code></strong>
*************************** 1. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 3. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
...
</pre><p>
    This chapter covers use cases for special-purpose MySQL storage
    engines. It does not cover the default
    <a class="link" href="innodb-storage-engine.html" title="Chapter 15 The InnoDB Storage Engine"><code class="literal">InnoDB</code></a> storage engine or the
    <a class="ulink" href="http://dev.mysql.com/doc/refman/5.7/en/mysql-cluster.html" target="_top"><code class="literal">NDB</code></a> storage engine which are covered in
    <a class="xref" href="innodb-storage-engine.html" title="Chapter 15 The InnoDB Storage Engine">Chapter 15, <i>The InnoDB Storage Engine</i></a> and
    <a class="ulink" href="http://dev.mysql.com/doc/refman/5.7/en/mysql-cluster.html" target="_top">MySQL NDB Cluster 7.5 and NDB Cluster 7.6</a>. For advanced users, it also
    contains a description of the pluggable storage engine architecture
    (see <a class="xref" href="storage-engines.html#pluggable-storage-overview" title="16.11 Overview of MySQL Storage Engine Architecture">Section 16.11, “Overview of MySQL Storage Engine Architecture”</a>).
  </p><p>
    For information about features offered in commercial MySQL Server
    binaries, see
    <a class="ulink" href="https://www.mysql.com/products/" target="_top"><em class="citetitle">MySQL
    Editions</em></a>, on the MySQL website. The storage
    engines available might depend on which edition of MySQL you are
    using.
  </p><p>
    For answers to commonly asked questions about MySQL storage engines,
    see <a class="xref" href="faqs.html#faqs-storage-engines" title="A.2 MySQL 8.0 FAQ: Storage Engines">Section A.2, “MySQL 8.0 FAQ: Storage Engines”</a>.
</p>
<h2><a name="idm139899505466320"></a>MySQL 8.0 Supported Storage Engines</h2>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
        <a class="link" href="innodb-storage-engine.html" title="Chapter 15 The InnoDB Storage Engine"><code class="literal">InnoDB</code></a>:
        The default storage engine in MySQL 8.0.
        <code class="literal">InnoDB</code> is a transaction-safe (ACID compliant)
        storage engine for MySQL that has commit, rollback, and
        crash-recovery capabilities to protect user data.
        <code class="literal">InnoDB</code> row-level locking (without escalation
        to coarser granularity locks) and Oracle-style consistent
        nonlocking reads increase multi-user concurrency and
        performance. <code class="literal">InnoDB</code> stores user data in
        clustered indexes to reduce I/O for common queries based on
        primary keys. To maintain data integrity,
        <code class="literal">InnoDB</code> also supports <code class="literal">FOREIGN
        KEY</code> referential-integrity constraints. For more
        information about <code class="literal">InnoDB</code>, see
        <a class="xref" href="innodb-storage-engine.html" title="Chapter 15 The InnoDB Storage Engine">Chapter 15, <i>The InnoDB Storage Engine</i></a>.
      </p></li><li class="listitem"><p>
        <a class="link" href="storage-engines.html#myisam-storage-engine" title="16.2 The MyISAM Storage Engine"><code class="literal">MyISAM</code></a>:
        These tables have a small footprint.
        <a class="link" href="glossary.html#glos_table_lock" title="table lock">Table-level locking</a>
        limits the performance in read/write workloads, so it is often
        used in read-only or read-mostly workloads in Web and data
        warehousing configurations.
      </p></li><li class="listitem"><p>
        <a class="link" href="storage-engines.html#memory-storage-engine" title="16.3 The MEMORY Storage Engine"><code class="literal">Memory</code></a>:
        Stores all data in RAM, for fast access in environments that
        require quick lookups of non-critical data. This engine was
        formerly known as the <code class="literal">HEAP</code> engine. Its use
        cases are decreasing; <code class="literal">InnoDB</code> with its buffer
        pool memory area provides a general-purpose and durable way to
        keep most or all data in memory, and
        <code class="literal">NDBCLUSTER</code> provides fast key-value lookups
        for huge distributed data sets.
      </p></li><li class="listitem"><p>
        <a class="link" href="storage-engines.html#csv-storage-engine" title="16.4 The CSV Storage Engine"><code class="literal">CSV</code></a>:
        Its tables are really text files with comma-separated values.
        CSV tables let you import or dump data in CSV format, to
        exchange data with scripts and applications that read and write
        that same format. Because CSV tables are not indexed, you
        typically keep the data in <code class="literal">InnoDB</code> tables
        during normal operation, and only use CSV tables during the
        import or export stage.
      </p></li><li class="listitem"><p>
        <a class="link" href="storage-engines.html#archive-storage-engine" title="16.5 The ARCHIVE Storage Engine"><code class="literal">Archive</code></a>:
        These compact, unindexed tables are intended for storing and
        retrieving large amounts of seldom-referenced historical,
        archived, or security audit information.
      </p></li><li class="listitem"><p>
        <a class="link" href="storage-engines.html#blackhole-storage-engine" title="16.6 The BLACKHOLE Storage Engine"><code class="literal">Blackhole</code></a>:
        The Blackhole storage engine accepts but does not store data,
        similar to the Unix <code class="literal">/dev/null</code> device. Queries
        always return an empty set. These tables can be used in
        replication configurations where DML statements are sent to
        slave servers, but the master server does not keep its own copy
        of the data.
      </p></li><li class="listitem"><p>
        <a class="link" href="storage-engines.html#merge-storage-engine" title="16.7 The MERGE Storage Engine"><code class="literal">Merge</code></a>:
        Enables a MySQL DBA or developer to logically group a series of
        identical <code class="literal">MyISAM</code> tables and reference them as
        one object. Good for VLDB environments such as data warehousing.
      </p></li><li class="listitem"><p>
        <a class="link" href="storage-engines.html#federated-storage-engine" title="16.8 The FEDERATED Storage Engine"><code class="literal">Federated</code></a>:
        Offers the ability to link separate MySQL servers to create one
        logical database from many physical servers. Very good for
        distributed or data mart environments.
      </p></li><li class="listitem"><p>
        <a class="link" href="storage-engines.html#example-storage-engine" title="16.9 The EXAMPLE Storage Engine"><code class="literal">Example</code></a>:
        This engine serves as an example in the MySQL source code that
        illustrates how to begin writing new storage engines. It is
        primarily of interest to developers. The storage engine is a
        <span class="quote">“<span class="quote">stub</span>”</span> that does nothing. You can create tables
        with this engine, but no data can be stored in them or retrieved
        from them.
</p></li></ul>
</div>
<p>
    You are not restricted to using the same storage engine for an
    entire server or schema. You can specify the storage engine for any
    table. For example, an application might use mostly
    <code class="literal">InnoDB</code> tables, with one <code class="literal">CSV</code>
    table for exporting data to a spreadsheet and a few
    <code class="literal">MEMORY</code> tables for temporary workspaces.
  </p><p>
    <span class="bold"><strong>Choosing a Storage Engine</strong></span>
  </p><p>
    The various storage engines provided with MySQL are designed with
    different use cases in mind. The following table provides an
    overview of some storage engines provided with MySQL, with
    clarifying notes following the table.
</p>
<div class="table">
<a name="idm139899505429248"></a><p class="title"><b>Table 16.1 Storage Engines Feature Summary</b></p>
<div class="table-contents">
<table frame="box" rules="all" summary="Summary of features supported per storage engine."><col width="10%"><col width="16%"><col width="16%"><col width="16%"><col width="16%"><col width="16%"><thead><tr><th scope="col">Feature</th>
<th scope="col">MyISAM</th>
<th scope="col">Memory</th>
<th scope="col">InnoDB</th>
<th scope="col">Archive</th>
<th scope="col">NDB</th>
</tr></thead><tbody><tr><td scope="row"><span class="bold"><strong>B-tree indexes</strong></span></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td>No</td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Backup/point-in-time recovery</strong></span> (note 1)</td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong>Cluster database support</strong></span></td>
<td>No</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong>Clustered indexes</strong></span></td>
<td>No</td>
<td>No</td>
<td>Yes</td>
<td>No</td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Compressed data</strong></span></td>
<td>Yes (note 2)</td>
<td>No</td>
<td>Yes</td>
<td>Yes</td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Data caches</strong></span></td>
<td>No</td>
<td>N/A</td>
<td>Yes</td>
<td>No</td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong>Encrypted data</strong></span> (note 3)</td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong>Foreign key support</strong></span></td>
<td>No</td>
<td>No</td>
<td>Yes</td>
<td>No</td>
<td>Yes (note 4)</td>
</tr><tr><td scope="row"><span class="bold"><strong>Full-text search indexes</strong></span></td>
<td>Yes</td>
<td>No</td>
<td>Yes (note 5)</td>
<td>No</td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Geospatial data type support</strong></span></td>
<td>Yes</td>
<td>No</td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong>Geospatial indexing support</strong></span></td>
<td>Yes</td>
<td>No</td>
<td>Yes (note 6)</td>
<td>No</td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Hash indexes</strong></span></td>
<td>No</td>
<td>Yes</td>
<td>No (note 7)</td>
<td>No</td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong>Index caches</strong></span></td>
<td>Yes</td>
<td>N/A</td>
<td>Yes</td>
<td>No</td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong>Locking granularity</strong></span></td>
<td>Table</td>
<td>Table</td>
<td>Row</td>
<td>Row</td>
<td>Row</td>
</tr><tr><td scope="row"><span class="bold"><strong>MVCC</strong></span></td>
<td>No</td>
<td>No</td>
<td>Yes</td>
<td>No</td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Query cache support</strong></span></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong>Replication support</strong></span> (note 1)</td>
<td>Yes</td>
<td>Limited (note 8)</td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong>Storage limits</strong></span></td>
<td>256TB</td>
<td>RAM</td>
<td>64TB</td>
<td>None</td>
<td>384EB</td>
</tr><tr><td scope="row"><span class="bold"><strong>T-tree indexes</strong></span></td>
<td>No</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong>Transactions</strong></span></td>
<td>No</td>
<td>No</td>
<td>Yes</td>
<td>No</td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong>Update statistics for data dictionary</strong></span></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
</tr></tbody></table>
</div>

</div>
<br class="table-break"><p><span class="bold"><strong>Notes:</strong></span></p><p>1. Implemented in the server, rather than in the storage engine.</p><p>2. Compressed MyISAM tables are supported only when using the compressed row format. Tables using the compressed row format with MyISAM are read only.</p><p>3. Implemented in the server via encryption functions. Data-at-rest tablespace encryption is available in MySQL 5.7 and later.</p><p>4. Support for foreign keys is available in MySQL Cluster NDB 7.3 and later.</p><p>5. InnoDB support for FULLTEXT indexes is available in MySQL 5.6 and later.</p><p>6. InnoDB support for geospatial indexing is available in MySQL 5.7 and later.</p><p>7. InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature.</p><p>8. See the discussion later in this section.</p>
<div class="section">

<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="storage-engine-setting"></a>16.1 Setting the Storage Engine</h2>

</div>

</div>

</div>
<p>
      When you create a new table, you can specify which storage engine
      to use by adding an <code class="literal">ENGINE</code> table option to the
      <a class="link" href="sql-syntax.html#create-table" title="13.1.18 CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a> statement:
    </p><pre data-lang="sql" class="programlisting">
-- ENGINE=INNODB not needed unless you have set a different
-- default storage engine.
CREATE TABLE t1 (i INT) ENGINE = INNODB;
-- Simple table definitions can be switched from one to another.
CREATE TABLE t2 (i INT) ENGINE = CSV;
CREATE TABLE t3 (i INT) ENGINE = MEMORY;
</pre><p>
      When you omit the <code class="literal">ENGINE</code> option, the default
      storage engine is used. The default engine is
      <a class="link" href="innodb-storage-engine.html" title="Chapter 15 The InnoDB Storage Engine"><code class="literal">InnoDB</code></a> in MySQL 8.0. You
      can specify the default engine by using the
      <a class="link" href="server-administration.html#option_mysqld_default-storage-engine"><code class="option">--default-storage-engine</code></a> server
      startup option, or by setting the
      <a class="link" href="server-administration.html#option_mysqld_default-storage-engine"><code class="option">default-storage-engine</code></a> option in
      the <code class="filename">my.cnf</code> configuration file.
    </p><p>
      You can set the default storage engine for the current session by
      setting the
      <a class="link" href="server-administration.html#sysvar_default_storage_engine"><code class="literal">default_storage_engine</code></a> variable:
    </p><pre data-lang="sql" class="programlisting">
SET default_storage_engine=NDBCLUSTER;
</pre><p>
      The storage engine for <code class="literal">TEMPORARY</code> tables created
      with <a class="link" href="sql-syntax.html#create-table" title="13.1.18 CREATE TABLE Syntax"><code class="literal">CREATE
      TEMPORARY TABLE</code></a> can be set separately from the engine
      for permanent tables by setting the
      <a class="link" href="server-administration.html#sysvar_default_tmp_storage_engine"><code class="literal">default_tmp_storage_engine</code></a>,
      either at startup or at runtime.
    </p><p>
      To convert a table from one storage engine to another, use an
      <a class="link" href="sql-syntax.html#alter-table" title="13.1.8 ALTER TABLE Syntax"><code class="literal">ALTER TABLE</code></a> statement that
      indicates the new engine:
    </p><pre data-lang="sql" class="programlisting">
ALTER TABLE t ENGINE = InnoDB;
</pre><p>
      See <a class="xref" href="sql-syntax.html#create-table" title="13.1.18 CREATE TABLE Syntax">Section 13.1.18, “CREATE TABLE Syntax”</a>, and
      <a class="xref" href="sql-syntax.html#alter-table" title="13.1.8 ALTER TABLE Syntax">Section 13.1.8, “ALTER TABLE Syntax”</a>.
    </p><p>
      If you try to use a storage engine that is not compiled in or that
      is compiled in but deactivated, MySQL instead creates a table
      using the default storage engine. For example, in a replication
      setup, perhaps your master server uses <code class="literal">InnoDB</code>
      tables for maximum safety, but the slave servers use other storage
      engines for speed at the expense of durability or concurrency.
    </p><p>
      By default, a warning is generated whenever
      <a class="link" href="sql-syntax.html#create-table" title="13.1.18 CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a> or
      <a class="link" href="sql-syntax.html#alter-table" title="13.1.8 ALTER TABLE Syntax"><code class="literal">ALTER TABLE</code></a> cannot use the default
      storage engine. To prevent confusing, unintended behavior if the
      desired engine is unavailable, enable the
      <a class="link" href="server-administration.html#sqlmode_no_engine_substitution"><code class="literal">NO_ENGINE_SUBSTITUTION</code></a> SQL mode.
      If the desired engine is unavailable, this setting produces an
      error instead of a warning, and the table is not created or
      altered. See <a class="xref" href="server-administration.html#sql-mode" title="5.1.8 Server SQL Modes">Section 5.1.8, “Server SQL Modes”</a>.
    </p><p>
      MySQL may store a table's index and data in one or more other
      files, depending on the storage engine. Table and column
      definitions are stored in the MySQL data dictionary. Individual
      storage engines create any additional files required for the
      tables that they manage. If a table name contains special
      characters, the names for the table files contain encoded versions
      of those characters as described in
      <a class="xref" href="language-structure.html#identifier-mapping" title="9.2.3 Mapping of Identifiers to File Names">Section 9.2.3, “Mapping of Identifiers to File Names”</a>.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="myisam-storage-engine"></a>16.2 The MyISAM Storage Engine</h2>

</div>

</div>

</div>
<div class="toc">
<dl class="toc"><dt><span class="section"><a href="storage-engines.html#myisam-start">16.2.1 MyISAM Startup Options</a></span></dt><dt><span class="section"><a href="storage-engines.html#key-space">16.2.2 Space Needed for Keys</a></span></dt><dt><span class="section"><a href="storage-engines.html#myisam-table-formats">16.2.3 MyISAM Table Storage Formats</a></span></dt><dt><span class="section"><a href="storage-engines.html#myisam-table-problems">16.2.4 MyISAM Table Problems</a></span></dt></dl>
</div>
<a class="indexterm" name="idm139899505251296"></a><a class="indexterm" name="idm139899505250256"></a><p>
    <code class="literal">MyISAM</code> is based on the older (and no longer
    available) <code class="literal">ISAM</code> storage engine but has many
    useful extensions.
</p>
<div class="table">
<a name="idm139899505246880"></a><p class="title"><b>Table 16.2 MyISAM Storage Engine Features</b></p>
<div class="table-contents">
<table frame="box" rules="all" summary="Features supported by the MyISAM storage engine."><col width="60%"><col width="40%"><thead><tr><th scope="col">Feature</th>
<th scope="col">Support</th>
</tr></thead><tbody><tr><td scope="row"><span class="bold"><strong>B-tree indexes</strong></span></td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong>Backup/point-in-time recovery</strong></span> (Implemented in the server, rather than in the storage engine.)</td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong>Cluster database support</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Clustered indexes</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Compressed data</strong></span></td>
<td>Yes (Compressed MyISAM tables are supported only when using the compressed row format. Tables using the compressed row format with MyISAM are read only.)</td>
</tr><tr><td scope="row"><span class="bold"><strong>Data caches</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Encrypted data</strong></span> (Implemented in the server via encryption functions. Data-at-rest tablespace encryption is available in MySQL 5.7 and later.)</td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong>Foreign key support</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Full-text search indexes</strong></span></td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong>Geospatial data type support</strong></span></td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong>Geospatial indexing support</strong></span></td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong>Hash indexes</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Index caches</strong></span></td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong>Locking granularity</strong></span></td>
<td>Table</td>
</tr><tr><td scope="row"><span class="bold"><strong>MVCC</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Query cache support</strong></span></td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong>Replication support</strong></span> (Implemented in the server, rather than in the storage engine.)</td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong>Storage limits</strong></span></td>
<td>256TB</td>
</tr><tr><td scope="row"><span class="bold"><strong>T-tree indexes</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Transactions</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Update statistics for data dictionary</strong></span></td>
<td>Yes</td>
</tr></tbody></table>
</div>

</div>
<br class="table-break"><p>
    Each <code class="literal">MyISAM</code> table is stored on disk in two files.
    The files have names that begin with the table name and have an
    extension to indicate the file type. The data file has an
    <code class="filename">.MYD</code> (<code class="literal">MYData</code>) extension. The
    index file has an <code class="filename">.MYI</code>
    (<code class="literal">MYIndex</code>) extension. The table definition is
    stored in the MySQL data dictionary.
  </p><p>
    To specify explicitly that you want a <code class="literal">MyISAM</code>
    table, indicate that with an <code class="literal">ENGINE</code> table option:
  </p><pre data-lang="sql" class="programlisting">
CREATE TABLE t (i INT) ENGINE = MYISAM;
</pre><p>
    In MySQL 8.0, it is normally necessary to use
    <code class="literal">ENGINE</code> to specify the <code class="literal">MyISAM</code>
    storage engine because <code class="literal">InnoDB</code> is the default
    engine.
  </p><p>
    You can check or repair <code class="literal">MyISAM</code> tables with the
    <a class="link" href="programs.html#mysqlcheck" title="4.5.3 mysqlcheck — A Table Maintenance Program"><span class="command"><strong>mysqlcheck</strong></span></a> client or <a class="link" href="programs.html#myisamchk" title="4.6.4 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a>
    utility. You can also compress <code class="literal">MyISAM</code> tables with
    <a class="link" href="programs.html#myisampack" title="4.6.6 myisampack — Generate Compressed, Read-Only MyISAM Tables"><span class="command"><strong>myisampack</strong></span></a> to take up much less space. See
    <a class="xref" href="programs.html#mysqlcheck" title="4.5.3 mysqlcheck — A Table Maintenance Program">Section 4.5.3, “<span class="command"><strong>mysqlcheck</strong></span> — A Table Maintenance Program”</a>, <a class="xref" href="programs.html#myisamchk" title="4.6.4 myisamchk — MyISAM Table-Maintenance Utility">Section 4.6.4, “<span class="command"><strong>myisamchk</strong></span> — MyISAM Table-Maintenance Utility”</a>, and
    <a class="xref" href="programs.html#myisampack" title="4.6.6 myisampack — Generate Compressed, Read-Only MyISAM Tables">Section 4.6.6, “<span class="command"><strong>myisampack</strong></span> — Generate Compressed, Read-Only MyISAM Tables”</a>.
  </p><p>
    In MySQL 8.0, the <code class="literal">MyISAM</code> storage
    engine provides no partitioning support. <span class="emphasis"><em>Partitioned
    <code class="literal">MyISAM</code> tables created in previous versions of
    MySQL cannot be used in MySQL 8.0</em></span>. For more
    information, see
    <a class="xref" href="partitioning.html#partitioning-limitations-storage-engines" title="22.6.2 Partitioning Limitations Relating to Storage Engines">Section 22.6.2, “Partitioning Limitations Relating to Storage Engines”</a>. For help
    with upgrading such tables so that they can be used in MySQL
    8.0, see
    <a class="xref" href="installing.html#upgrading-from-previous-series" title="2.11.1.2 Changes Affecting Upgrades to MySQL 8.0">Section 2.11.1.2, “Changes Affecting Upgrades to MySQL 8.0”</a>.
  </p><p>
    <code class="literal">MyISAM</code> tables have the following characteristics:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
        All data values are stored with the low byte first. This makes
        the data machine and operating system independent. The only
        requirements for binary portability are that the machine uses
        two's-complement signed integers and IEEE floating-point format.
        These requirements are widely used among mainstream machines.
        Binary compatibility might not be applicable to embedded
        systems, which sometimes have peculiar processors.
      </p><p>
        There is no significant speed penalty for storing data low byte
        first; the bytes in a table row normally are unaligned and it
        takes little more processing to read an unaligned byte in order
        than in reverse order. Also, the code in the server that fetches
        column values is not time critical compared to other code.
      </p></li><li class="listitem"><p>
        All numeric key values are stored with the high byte first to
        permit better index compression.
      </p></li><li class="listitem"><p>
        Large files (up to 63-bit file length) are supported on file
        systems and operating systems that support large files.
      </p></li><li class="listitem"><p>
        There is a limit of
        (2<sup>32</sup>)<sup>2</sup>
        (1.844E+19) rows in a <code class="literal">MyISAM</code> table.
      </p></li><li class="listitem"><p>
        The maximum number of indexes per <code class="literal">MyISAM</code>
        table is 64.
      </p><p>
        The maximum number of columns per index is 16.
      </p></li><li class="listitem"><p>
        The maximum key length is 1000 bytes. This can also be changed
        by changing the source and recompiling. For the case of a key
        longer than 250 bytes, a larger key block size than the default
        of 1024 bytes is used.
      </p></li><li class="listitem"><p>
        When rows are inserted in sorted order (as when you are using an
        <code class="literal">AUTO_INCREMENT</code> column), the index tree is
        split so that the high node only contains one key. This improves
        space utilization in the index tree.
      </p></li><li class="listitem"><p>
        Internal handling of one <code class="literal">AUTO_INCREMENT</code>
        column per table is supported. <code class="literal">MyISAM</code>
        automatically updates this column for
        <a class="link" href="sql-syntax.html#insert" title="13.2.6 INSERT Syntax"><code class="literal">INSERT</code></a> and
        <a class="link" href="sql-syntax.html#update" title="13.2.12 UPDATE Syntax"><code class="literal">UPDATE</code></a> operations. This makes
        <code class="literal">AUTO_INCREMENT</code> columns faster (at least 10%).
        Values at the top of the sequence are not reused after being
        deleted. (When an <code class="literal">AUTO_INCREMENT</code> column is
        defined as the last column of a multiple-column index, reuse of
        values deleted from the top of a sequence does occur.) The
        <code class="literal">AUTO_INCREMENT</code> value can be reset with
        <a class="link" href="sql-syntax.html#alter-table" title="13.1.8 ALTER TABLE Syntax"><code class="literal">ALTER TABLE</code></a> or
        <a class="link" href="programs.html#myisamchk" title="4.6.4 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a>.
      </p></li><li class="listitem"><p>
        Dynamic-sized rows are much less fragmented when mixing deletes
        with updates and inserts. This is done by automatically
        combining adjacent deleted blocks and by extending blocks if the
        next block is deleted.
      </p></li><li class="listitem"><p>
        <code class="literal">MyISAM</code> supports concurrent inserts: If a
        table has no free blocks in the middle of the data file, you can
        <a class="link" href="sql-syntax.html#insert" title="13.2.6 INSERT Syntax"><code class="literal">INSERT</code></a> new rows into it at the
        same time that other threads are reading from the table. A free
        block can occur as a result of deleting rows or an update of a
        dynamic length row with more data than its current contents.
        When all free blocks are used up (filled in), future inserts
        become concurrent again. See
        <a class="xref" href="optimization.html#concurrent-inserts" title="8.11.3 Concurrent Inserts">Section 8.11.3, “Concurrent Inserts”</a>.
      </p></li><li class="listitem"><p>
        You can put the data file and index file in different
        directories on different physical devices to get more speed with
        the <code class="literal">DATA DIRECTORY</code> and <code class="literal">INDEX
        DIRECTORY</code> table options to <a class="link" href="sql-syntax.html#create-table" title="13.1.18 CREATE TABLE Syntax"><code class="literal">CREATE
        TABLE</code></a>. See <a class="xref" href="sql-syntax.html#create-table" title="13.1.18 CREATE TABLE Syntax">Section 13.1.18, “CREATE TABLE Syntax”</a>.
      </p></li><li class="listitem"><p>
        <a class="link" href="data-types.html#blob" title="11.4.3 The BLOB and TEXT Types"><code class="literal">BLOB</code></a> and
        <a class="link" href="data-types.html#blob" title="11.4.3 The BLOB and TEXT Types"><code class="literal">TEXT</code></a> columns can be indexed.
      </p></li><li class="listitem"><p>
        <code class="literal">NULL</code> values are permitted in indexed columns.
        This takes 0 to 1 bytes per key.
      </p></li><li class="listitem"><p>
        Each character column can have a different character set. See
        <a class="xref" href="charset.html" title="Chapter 10 Character Sets, Collations, Unicode">Chapter 10, <i>Character Sets, Collations, Unicode</i></a>.
      </p></li><li class="listitem"><p>
        There is a flag in the <code class="literal">MyISAM</code> index file that
        indicates whether the table was closed correctly. If
        <a class="link" href="programs.html#mysqld" title="4.3.1 mysqld — The MySQL Server"><span class="command"><strong>mysqld</strong></span></a> is started with the
        <a class="link" href="server-administration.html#option_mysqld_myisam-recover-options"><code class="option">--myisam-recover-options</code></a> option,
        <code class="literal">MyISAM</code> tables are automatically checked when
        opened, and are repaired if the table wasn't closed properly.
      </p></li><li class="listitem"><p>
        <a class="link" href="programs.html#myisamchk" title="4.6.4 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a> marks tables as checked if you run
        it with the <a class="link" href="programs.html#option_myisamchk_update-state"><code class="option">--update-state</code></a>
        option. <a class="link" href="programs.html#myisamchk" title="4.6.4 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk --fast</strong></span></a> checks only those
        tables that don't have this mark.
      </p></li><li class="listitem"><p>
        <a class="link" href="programs.html#myisamchk" title="4.6.4 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk --analyze</strong></span></a> stores statistics for
        portions of keys, as well as for entire keys.
      </p></li><li class="listitem"><p>
        <a class="link" href="programs.html#myisampack" title="4.6.6 myisampack — Generate Compressed, Read-Only MyISAM Tables"><span class="command"><strong>myisampack</strong></span></a> can pack
        <a class="link" href="data-types.html#blob" title="11.4.3 The BLOB and TEXT Types"><code class="literal">BLOB</code></a> and
        <a class="link" href="data-types.html#char" title="11.4.1 The CHAR and VARCHAR Types"><code class="literal">VARCHAR</code></a> columns.
</p></li></ul>
</div>
<p>
    <code class="literal">MyISAM</code> also supports the following features:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
        Support for a true <a class="link" href="data-types.html#char" title="11.4.1 The CHAR and VARCHAR Types"><code class="literal">VARCHAR</code></a> type;
        a <a class="link" href="data-types.html#char" title="11.4.1 The CHAR and VARCHAR Types"><code class="literal">VARCHAR</code></a> column starts with a
        length stored in one or two bytes.
      </p></li><li class="listitem"><p>
        Tables with <a class="link" href="data-types.html#char" title="11.4.1 The CHAR and VARCHAR Types"><code class="literal">VARCHAR</code></a> columns may
        have fixed or dynamic row length.
      </p></li><li class="listitem"><p>
        The sum of the lengths of the
        <a class="link" href="data-types.html#char" title="11.4.1 The CHAR and VARCHAR Types"><code class="literal">VARCHAR</code></a> and
        <a class="link" href="data-types.html#char" title="11.4.1 The CHAR and VARCHAR Types"><code class="literal">CHAR</code></a> columns in a table may be up
        to 64KB.
      </p></li><li class="listitem"><p>
        Arbitrary length <code class="literal">UNIQUE</code> constraints.
</p></li></ul>
</div>
<h3><a name="idm139899505097456"></a>Additional Resources</h3>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
        A forum dedicated to the <code class="literal">MyISAM</code> storage
        engine is available at <a class="ulink" href="http://forums.mysql.com/list.php?21" target="_top">http://forums.mysql.com/list.php?21</a>.
</p></li></ul>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="myisam-start"></a>16.2.1 MyISAM Startup Options</h3>

</div>

</div>

</div>
<p>
      The following options to <a class="link" href="programs.html#mysqld" title="4.3.1 mysqld — The MySQL Server"><span class="command"><strong>mysqld</strong></span></a> can be used to
      change the behavior of <code class="literal">MyISAM</code> tables. For
      additional information, see <a class="xref" href="server-administration.html#server-options" title="5.1.4 Server Command Options">Section 5.1.4, “Server Command Options”</a>.
</p>
<div class="table">
<a name="idm139899505090080"></a><p class="title"><b>Table 16.3 MyISAM Option and Variable Reference</b></p>
<div class="table-contents">
<table frame="box" rules="all" summary="Reference for MyISAM command-line options and system variables."><col width="20%"><col width="15%"><col width="15%"><col width="15%"><col width="15%"><col width="15%"><col width="15%"><thead><tr><th scope="col">Name</th>
<th scope="col">Cmd-Line</th>
<th scope="col">Option File</th>
<th scope="col">System Var</th>
<th scope="col">Status Var</th>
<th scope="col">Var Scope</th>
<th scope="col">Dynamic</th>
</tr></thead><tbody><tr><td scope="row"><a class="link" href="server-administration.html#sysvar_bulk_insert_buffer_size">bulk_insert_buffer_size</a></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td></td>
<td>Both</td>
<td>Yes</td>
</tr><tr><td scope="row"><a class="link" href="server-administration.html#sysvar_concurrent_insert">concurrent_insert</a></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td></td>
<td>Global</td>
<td>Yes</td>
</tr><tr><td scope="row"><a class="link" href="server-administration.html#option_mysqld_delay-key-write">delay-key-write</a></td>
<td>Yes</td>
<td>Yes</td>
<td></td>
<td></td>
<td>Global</td>
<td>Yes</td>
</tr><tr><td scope="row"> - <span class="emphasis"><em>Variable</em></span>: <a class="link" href="server-administration.html#sysvar_delay_key_write">delay_key_write</a></td>
<td></td>
<td></td>
<td>Yes</td>
<td></td>
<td>Global</td>
<td>Yes</td>
</tr><tr><td scope="row"><a class="link" href="server-administration.html#sysvar_have_rtree_keys">have_rtree_keys</a></td>
<td></td>
<td></td>
<td>Yes</td>
<td></td>
<td>Global</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="server-administration.html#sysvar_key_buffer_size">key_buffer_size</a></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td></td>
<td>Global</td>
<td>Yes</td>
</tr><tr><td scope="row"><a class="link" href="server-administration.html#option_mysqld_log-isam">log-isam</a></td>
<td>Yes</td>
<td>Yes</td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr><tr><td scope="row"><a class="link" href="server-administration.html#option_mysqld_myisam-block-size">myisam-block-size</a></td>
<td>Yes</td>
<td>Yes</td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr><tr><td scope="row"><a class="link" href="server-administration.html#sysvar_myisam_data_pointer_size">myisam_data_pointer_size</a></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td></td>
<td>Global</td>
<td>Yes</td>
</tr><tr><td scope="row"><a class="link" href="server-administration.html#sysvar_myisam_max_sort_file_size">myisam_max_sort_file_size</a></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td></td>
<td>Global</td>
<td>Yes</td>
</tr><tr><td scope="row"><a class="link" href="server-administration.html#sysvar_myisam_mmap_size">myisam_mmap_size</a></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td></td>
<td>Global</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="server-administration.html#option_mysqld_myisam-recover-options">myisam-recover-options</a></td>
<td>Yes</td>
<td>Yes</td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr><tr><td scope="row"> - <span class="emphasis"><em>Variable</em></span>: <a class="link" href="server-administration.html#sysvar_myisam_recover_options">myisam_recover_options</a></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr><tr><td scope="row"><a class="link" href="server-administration.html#sysvar_myisam_recover_options">myisam_recover_options</a></td>
<td></td>
<td></td>
<td>Yes</td>
<td></td>
<td>Global</td>
<td>No</td>
</tr><tr><td scope="row"><a class="link" href="server-administration.html#sysvar_myisam_repair_threads">myisam_repair_threads</a></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td></td>
<td>Both</td>
<td>Yes</td>
</tr><tr><td scope="row"><a class="link" href="server-administration.html#sysvar_myisam_sort_buffer_size">myisam_sort_buffer_size</a></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td></td>
<td>Both</td>
<td>Yes</td>
</tr><tr><td scope="row"><a class="link" href="server-administration.html#sysvar_myisam_stats_method">myisam_stats_method</a></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td></td>
<td>Both</td>
<td>Yes</td>
</tr><tr><td scope="row"><a class="link" href="server-administration.html#sysvar_myisam_use_mmap">myisam_use_mmap</a></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td></td>
<td>Global</td>
<td>Yes</td>
</tr><tr><td scope="row"><a class="link" href="server-administration.html#option_mysqld_skip-concurrent-insert">skip-concurrent-insert</a></td>
<td>Yes</td>
<td>Yes</td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr><tr><td scope="row"> - <span class="emphasis"><em>Variable</em></span>: <a class="link" href="server-administration.html#sysvar_concurrent_insert">concurrent_insert</a></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr><tr><td scope="row"><a class="link" href="server-administration.html#sysvar_tmp_table_size">tmp_table_size</a></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td></td>
<td>Both</td>
<td>Yes</td>
</tr></tbody></table>
</div>

</div>
<br class="table-break">
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          <a class="indexterm" name="idm139899504928528"></a>

          <a class="indexterm" name="idm139899504927024"></a>

          <a class="link" href="server-administration.html#option_mysqld_myisam-recover-options"><code class="option">--myisam-recover-options=<em class="replaceable"><code>mode</code></em></code></a>
        </p><p>
          Set the mode for automatic recovery of crashed
          <code class="literal">MyISAM</code> tables.
        </p></li><li class="listitem"><p>
          <a class="indexterm" name="idm139899504922304"></a>

          <a class="indexterm" name="idm139899504920816"></a>

          <a class="link" href="server-administration.html#option_mysqld_delay-key-write"><code class="option">--delay-key-write=ALL</code></a>
        </p><p>
          Don't flush key buffers between writes for any
          <code class="literal">MyISAM</code> table.
</p>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Note
</div>
<p>
            If you do this, you should not access
            <code class="literal">MyISAM</code> tables from another program (such
            as from another MySQL server or with
            <a class="link" href="programs.html#myisamchk" title="4.6.4 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a>) when the tables are in use.
            Doing so risks index corruption. Using
            <a class="link" href="server-administration.html#option_mysqld_external-locking"><code class="option">--external-locking</code></a> does not
            eliminate this risk.
</p>
</div>
</li></ul>
</div>
<p>
      The following system variables affect the behavior of
      <code class="literal">MyISAM</code> tables. For additional information, see
      <a class="xref" href="server-administration.html#server-system-variables" title="5.1.5 Server System Variables">Section 5.1.5, “Server System Variables”</a>.
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          <a class="link" href="server-administration.html#sysvar_bulk_insert_buffer_size"><code class="literal">bulk_insert_buffer_size</code></a>
        </p><p>
          The size of the tree cache used in bulk insert optimization.
</p>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Note
</div>
<p>
            This is a limit <span class="emphasis"><em>per thread</em></span>!
</p>
</div>
</li><li class="listitem"><p>
          <a class="link" href="server-administration.html#sysvar_myisam_max_sort_file_size"><code class="literal">myisam_max_sort_file_size</code></a>
        </p><p>
          The maximum size of the temporary file that MySQL is permitted
          to use while re-creating a <code class="literal">MyISAM</code> index
          (during <a class="link" href="sql-syntax.html#repair-table" title="13.7.3.5 REPAIR TABLE Syntax"><code class="literal">REPAIR TABLE</code></a>,
          <a class="link" href="sql-syntax.html#alter-table" title="13.1.8 ALTER TABLE Syntax"><code class="literal">ALTER TABLE</code></a>, or
          <a class="link" href="sql-syntax.html#load-data" title="13.2.7 LOAD DATA INFILE Syntax"><code class="literal">LOAD DATA
          INFILE</code></a>). If the file size would be larger than this
          value, the index is created using the key cache instead, which
          is slower. The value is given in bytes.
        </p></li><li class="listitem"><p>
          <a class="link" href="server-administration.html#sysvar_myisam_sort_buffer_size"><code class="literal">myisam_sort_buffer_size</code></a>
        </p><p>
          Set the size of the buffer used when recovering tables.
</p></li></ul>
</div>
<p>
      Automatic recovery is activated if you start
      <a class="link" href="programs.html#mysqld" title="4.3.1 mysqld — The MySQL Server"><span class="command"><strong>mysqld</strong></span></a> with the
      <a class="link" href="server-administration.html#option_mysqld_myisam-recover-options"><code class="option">--myisam-recover-options</code></a> option. In
      this case, when the server opens a <code class="literal">MyISAM</code>
      table, it checks whether the table is marked as crashed or whether
      the open count variable for the table is not 0 and you are running
      the server with external locking disabled. If either of these
      conditions is true, the following happens:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          The server checks the table for errors.
        </p></li><li class="listitem"><p>
          If the server finds an error, it tries to do a fast table
          repair (with sorting and without re-creating the data file).
        </p></li><li class="listitem"><p>
          If the repair fails because of an error in the data file (for
          example, a duplicate-key error), the server tries again, this
          time re-creating the data file.
        </p></li><li class="listitem"><p>
          If the repair still fails, the server tries once more with the
          old repair option method (write row by row without sorting).
          This method should be able to repair any type of error and has
          low disk space requirements.
</p></li></ul>
</div>
<p>
      If the recovery wouldn't be able to recover all rows from
      previously completed statements and you didn't specify
      <code class="literal">FORCE</code> in the value of the
      <a class="link" href="server-administration.html#option_mysqld_myisam-recover-options"><code class="option">--myisam-recover-options</code></a> option,
      automatic repair aborts with an error message in the error log:
    </p><pre data-lang="none" class="programlisting">
Error: Couldn't repair table: test.g00pages
</pre><p>
      If you specify <code class="literal">FORCE</code>, a warning like this is
      written instead:
    </p><pre data-lang="none" class="programlisting">
Warning: Found 344 of 354 rows when repairing ./test/g00pages
</pre><p>
      If the automatic recovery value includes
      <code class="literal">BACKUP</code>, the recovery process creates files with
      names of the form
      <code class="filename"><em class="replaceable"><code>tbl_name-datetime</code></em>.BAK</code>.
      You should have a <span class="command"><strong>cron</strong></span> script that
      automatically moves these files from the database directories to
      backup media.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="key-space"></a>16.2.2 Space Needed for Keys</h3>

</div>

</div>

</div>
<a class="indexterm" name="idm139899504879424"></a><p>
      <code class="literal">MyISAM</code> tables use B-tree indexes. You can
      roughly calculate the size for the index file as
      <code class="literal">(key_length+4)/0.67</code>, summed over all keys. This
      is for the worst case when all keys are inserted in sorted order
      and the table doesn't have any compressed keys.
    </p><p>
      String indexes are space compressed. If the first index part is a
      string, it is also prefix compressed. Space compression makes the
      index file smaller than the worst-case figure if a string column
      has a lot of trailing space or is a
      <a class="link" href="data-types.html#char" title="11.4.1 The CHAR and VARCHAR Types"><code class="literal">VARCHAR</code></a> column that is not always
      used to the full length. Prefix compression is used on keys that
      start with a string. Prefix compression helps if there are many
      strings with an identical prefix.
    </p><p>
      In <code class="literal">MyISAM</code> tables, you can also prefix compress
      numbers by specifying the <code class="literal">PACK_KEYS=1</code> table
      option when you create the table. Numbers are stored with the high
      byte first, so this helps when you have many integer keys that
      have an identical prefix.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="myisam-table-formats"></a>16.2.3 MyISAM Table Storage Formats</h3>

</div>

</div>

</div>
<div class="toc">
<dl class="toc"><dt><span class="section"><a href="storage-engines.html#static-format">16.2.3.1 Static (Fixed-Length) Table Characteristics</a></span></dt><dt><span class="section"><a href="storage-engines.html#dynamic-format">16.2.3.2 Dynamic Table Characteristics</a></span></dt><dt><span class="section"><a href="storage-engines.html#compressed-format">16.2.3.3 Compressed Table Characteristics</a></span></dt></dl>
</div>
<p>
      <code class="literal">MyISAM</code> supports three different storage
      formats. Two of them, fixed and dynamic format, are chosen
      automatically depending on the type of columns you are using. The
      third, compressed format, can be created only with the
      <a class="link" href="programs.html#myisampack" title="4.6.6 myisampack — Generate Compressed, Read-Only MyISAM Tables"><span class="command"><strong>myisampack</strong></span></a> utility (see
      <a class="xref" href="programs.html#myisampack" title="4.6.6 myisampack — Generate Compressed, Read-Only MyISAM Tables">Section 4.6.6, “<span class="command"><strong>myisampack</strong></span> — Generate Compressed, Read-Only MyISAM Tables”</a>).
    </p><p>
      When you use <a class="link" href="sql-syntax.html#create-table" title="13.1.18 CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a> or
      <a class="link" href="sql-syntax.html#alter-table" title="13.1.8 ALTER TABLE Syntax"><code class="literal">ALTER TABLE</code></a> for a table that has no
      <a class="link" href="data-types.html#blob" title="11.4.3 The BLOB and TEXT Types"><code class="literal">BLOB</code></a> or
      <a class="link" href="data-types.html#blob" title="11.4.3 The BLOB and TEXT Types"><code class="literal">TEXT</code></a> columns, you can force the
      table format to <code class="literal">FIXED</code> or
      <code class="literal">DYNAMIC</code> with the <code class="literal">ROW_FORMAT</code>
      table option.
    </p><p>
      See <a class="xref" href="sql-syntax.html#create-table" title="13.1.18 CREATE TABLE Syntax">Section 13.1.18, “CREATE TABLE Syntax”</a>, for information about
      <code class="literal">ROW_FORMAT</code>.
    </p><p>
      You can decompress (unpack) compressed <code class="literal">MyISAM</code>
      tables using <a class="link" href="programs.html#myisamchk" title="4.6.4 myisamchk — MyISAM Table-Maintenance Utility">myisamchk
      <code class="option">--unpack</code></a>; see
      <a class="xref" href="programs.html#myisamchk" title="4.6.4 myisamchk — MyISAM Table-Maintenance Utility">Section 4.6.4, “<span class="command"><strong>myisamchk</strong></span> — MyISAM Table-Maintenance Utility”</a>, for more information.
</p>
<div class="section">

<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="static-format"></a>16.2.3.1 Static (Fixed-Length) Table Characteristics</h4>
</div>
</div>
</div>
<p>
        Static format is the default for <code class="literal">MyISAM</code>
        tables. It is used when the table contains no variable-length
        columns (<a class="link" href="data-types.html#char" title="11.4.1 The CHAR and VARCHAR Types"><code class="literal">VARCHAR</code></a>,
        <a class="link" href="data-types.html#binary-varbinary" title="11.4.2 The BINARY and VARBINARY Types"><code class="literal">VARBINARY</code></a>,
        <a class="link" href="data-types.html#blob" title="11.4.3 The BLOB and TEXT Types"><code class="literal">BLOB</code></a>, or
        <a class="link" href="data-types.html#blob" title="11.4.3 The BLOB and TEXT Types"><code class="literal">TEXT</code></a>). Each row is stored using a
        fixed number of bytes.
      </p><p>
        Of the three <code class="literal">MyISAM</code> storage formats, static
        format is the simplest and most secure (least subject to
        corruption). It is also the fastest of the on-disk formats due
        to the ease with which rows in the data file can be found on
        disk: To look up a row based on a row number in the index,
        multiply the row number by the row length to calculate the row
        position. Also, when scanning a table, it is very easy to read a
        constant number of rows with each disk read operation.
      </p><p>
        The security is evidenced if your computer crashes while the
        MySQL server is writing to a fixed-format
        <code class="literal">MyISAM</code> file. In this case,
        <a class="link" href="programs.html#myisamchk" title="4.6.4 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a> can easily determine where each row
        starts and ends, so it can usually reclaim all rows except the
        partially written one. <code class="literal">MyISAM</code> table indexes
        can always be reconstructed based on the data rows.
</p>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Note
</div>
<p>
          Fixed-length row format is only available for tables without
          <a class="link" href="data-types.html#blob" title="11.4.3 The BLOB and TEXT Types"><code class="literal">BLOB</code></a> or
          <a class="link" href="data-types.html#blob" title="11.4.3 The BLOB and TEXT Types"><code class="literal">TEXT</code></a> columns. Creating a table
          with these columns with an explicit
          <code class="literal">ROW_FORMAT</code> clause will not raise an error
          or warning; the format specification will be ignored.
</p>
</div>
<p>
        Static-format tables have these characteristics:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            <a class="link" href="data-types.html#char" title="11.4.1 The CHAR and VARCHAR Types"><code class="literal">CHAR</code></a> and
            <a class="link" href="data-types.html#char" title="11.4.1 The CHAR and VARCHAR Types"><code class="literal">VARCHAR</code></a> columns are
            space-padded to the specified column width, although the
            column type is not altered.
            <a class="link" href="data-types.html#binary-varbinary" title="11.4.2 The BINARY and VARBINARY Types"><code class="literal">BINARY</code></a> and
            <a class="link" href="data-types.html#binary-varbinary" title="11.4.2 The BINARY and VARBINARY Types"><code class="literal">VARBINARY</code></a> columns are padded
            with <code class="literal">0x00</code> bytes to the column width.
          </p></li><li class="listitem"><p>
            <code class="literal">NULL</code> columns require additional space in
            the row to record whether their values are
            <code class="literal">NULL</code>. Each <code class="literal">NULL</code> column
            takes one bit extra, rounded up to the nearest byte.
          </p></li><li class="listitem"><p>
            Very quick.
          </p></li><li class="listitem"><p>
            Easy to cache.
          </p></li><li class="listitem"><p>
            Easy to reconstruct after a crash, because rows are located
            in fixed positions.
          </p></li><li class="listitem"><p>
            Reorganization is unnecessary unless you delete a huge
            number of rows and want to return free disk space to the
            operating system. To do this, use
            <a class="link" href="sql-syntax.html#optimize-table" title="13.7.3.4 OPTIMIZE TABLE Syntax"><code class="literal">OPTIMIZE TABLE</code></a> or
            <a class="link" href="programs.html#myisamchk" title="4.6.4 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk -r</strong></span></a>.
          </p></li><li class="listitem"><p>
            Usually require more disk space than dynamic-format tables.
          </p></li><li class="listitem"><p>
            The expected row length in bytes for static-sized rows is
            calculated using the following expression:
          </p><pre data-lang="none" class="programlisting">
row length = 1
             + (<em class="replaceable"><code>sum of column lengths</code></em>)
             + (<em class="replaceable"><code>number of NULL columns</code></em> + <em class="replaceable"><code>delete_flag</code></em> + 7)/8
             + (<em class="replaceable"><code>number of variable-length columns</code></em>)
</pre><p>
            <em class="replaceable"><code>delete_flag</code></em> is 1 for tables with
            static row format. Static tables use a bit in the row record
            for a flag that indicates whether the row has been deleted.
            <em class="replaceable"><code>delete_flag</code></em> is 0 for dynamic
            tables because the flag is stored in the dynamic row header.
</p></li></ul>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="dynamic-format"></a>16.2.3.2 Dynamic Table Characteristics</h4>

</div>

</div>

</div>
<a class="indexterm" name="idm139899504813456"></a><a class="indexterm" name="idm139899504812400"></a><p>
        Dynamic storage format is used if a <code class="literal">MyISAM</code>
        table contains any variable-length columns
        (<a class="link" href="data-types.html#char" title="11.4.1 The CHAR and VARCHAR Types"><code class="literal">VARCHAR</code></a>,
        <a class="link" href="data-types.html#binary-varbinary" title="11.4.2 The BINARY and VARBINARY Types"><code class="literal">VARBINARY</code></a>,
        <a class="link" href="data-types.html#blob" title="11.4.3 The BLOB and TEXT Types"><code class="literal">BLOB</code></a>, or
        <a class="link" href="data-types.html#blob" title="11.4.3 The BLOB and TEXT Types"><code class="literal">TEXT</code></a>), or if the table was
        created with the <code class="literal">ROW_FORMAT=DYNAMIC</code> table
        option.
      </p><p>
        Dynamic format is a little more complex than static format
        because each row has a header that indicates how long it is. A
        row can become fragmented (stored in noncontiguous pieces) when
        it is made longer as a result of an update.
      </p><a class="indexterm" name="idm139899504803328"></a><p>
        You can use <a class="link" href="sql-syntax.html#optimize-table" title="13.7.3.4 OPTIMIZE TABLE Syntax"><code class="literal">OPTIMIZE TABLE</code></a> or
        <a class="link" href="programs.html#myisamchk" title="4.6.4 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk -r</strong></span></a> to defragment a table. If you
        have fixed-length columns that you access or change frequently
        in a table that also contains some variable-length columns, it
        might be a good idea to move the variable-length columns to
        other tables just to avoid fragmentation.
      </p><p>
        Dynamic-format tables have these characteristics:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            All string columns are dynamic except those with a length
            less than four.
          </p></li><li class="listitem"><p>
            Each row is preceded by a bitmap that indicates which
            columns contain the empty string (for string columns) or
            zero (for numeric columns). This does not include columns
            that contain <code class="literal">NULL</code> values. If a string
            column has a length of zero after trailing space removal, or
            a numeric column has a value of zero, it is marked in the
            bitmap and not saved to disk. Nonempty strings are saved as
            a length byte plus the string contents.
          </p></li><li class="listitem"><p>
            <code class="literal">NULL</code> columns require additional space in
            the row to record whether their values are
            <code class="literal">NULL</code>. Each <code class="literal">NULL</code> column
            takes one bit extra, rounded up to the nearest byte.
          </p></li><li class="listitem"><p>
            Much less disk space usually is required than for
            fixed-length tables.
          </p></li><li class="listitem"><p>
            Each row uses only as much space as is required. However, if
            a row becomes larger, it is split into as many pieces as are
            required, resulting in row fragmentation. For example, if
            you update a row with information that extends the row
            length, the row becomes fragmented. In this case, you may
            have to run <a class="link" href="sql-syntax.html#optimize-table" title="13.7.3.4 OPTIMIZE TABLE Syntax"><code class="literal">OPTIMIZE TABLE</code></a> or
            <a class="link" href="programs.html#myisamchk" title="4.6.4 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk -r</strong></span></a> from time to time to improve
            performance. Use <a class="link" href="programs.html#myisamchk" title="4.6.4 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk -ei</strong></span></a> to obtain
            table statistics.
          </p></li><li class="listitem"><p>
            More difficult than static-format tables to reconstruct
            after a crash, because rows may be fragmented into many
            pieces and links (fragments) may be missing.
          </p></li><li class="listitem"><p>
            The expected row length for dynamic-sized rows is calculated
            using the following expression:
          </p><pre data-lang="none" class="programlisting">
3
+ (<em class="replaceable"><code>number of columns</code></em> + 7) / 8
+ (<em class="replaceable"><code>number of char columns</code></em>)
+ (<em class="replaceable"><code>packed size of numeric columns</code></em>)
+ (<em class="replaceable"><code>length of strings</code></em>)
+ (<em class="replaceable"><code>number of NULL columns</code></em> + 7) / 8
</pre><p>
            There is a penalty of 6 bytes for each link. A dynamic row
            is linked whenever an update causes an enlargement of the
            row. Each new link is at least 20 bytes, so the next
            enlargement probably goes in the same link. If not, another
            link is created. You can find the number of links using
            <a class="link" href="programs.html#myisamchk" title="4.6.4 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk -ed</strong></span></a>. All links may be removed
            with <a class="link" href="sql-syntax.html#optimize-table" title="13.7.3.4 OPTIMIZE TABLE Syntax"><code class="literal">OPTIMIZE TABLE</code></a> or
            <a class="link" href="programs.html#myisamchk" title="4.6.4 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk -r</strong></span></a>.
</p></li></ul>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="compressed-format"></a>16.2.3.3 Compressed Table Characteristics</h4>

</div>

</div>

</div>
<a class="indexterm" name="idm139899504774816"></a><a class="indexterm" name="idm139899504773360"></a><a class="indexterm" name="idm139899504772288"></a><a class="indexterm" name="idm139899504770800"></a><p>
        Compressed storage format is a read-only format that is
        generated with the <a class="link" href="programs.html#myisampack" title="4.6.6 myisampack — Generate Compressed, Read-Only MyISAM Tables"><span class="command"><strong>myisampack</strong></span></a> tool.
        Compressed tables can be uncompressed with
        <a class="link" href="programs.html#myisamchk" title="4.6.4 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a>.
      </p><p>
        Compressed tables have the following characteristics:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            Compressed tables take very little disk space. This
            minimizes disk usage, which is helpful when using slow disks
            (such as CD-ROMs).
          </p></li><li class="listitem"><p>
            Each row is compressed separately, so there is very little
            access overhead. The header for a row takes up one to three
            bytes depending on the biggest row in the table. Each column
            is compressed differently. There is usually a different
            Huffman tree for each column. Some of the compression types
            are:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>
                Suffix space compression.
              </p></li><li class="listitem"><p>
                Prefix space compression.
              </p></li><li class="listitem"><p>
                Numbers with a value of zero are stored using one bit.
              </p></li><li class="listitem"><p>
                If values in an integer column have a small range, the
                column is stored using the smallest possible type. For
                example, a <a class="link" href="data-types.html#integer-types" title="11.2.1 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT"><code class="literal">BIGINT</code></a> column
                (eight bytes) can be stored as a
                <a class="link" href="data-types.html#integer-types" title="11.2.1 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT"><code class="literal">TINYINT</code></a> column (one byte)
                if all its values are in the range from
                <code class="literal">-128</code> to <code class="literal">127</code>.
              </p></li><li class="listitem"><p>
                If a column has only a small set of possible values, the
                data type is converted to
                <a class="link" href="data-types.html#enum" title="11.4.4 The ENUM Type"><code class="literal">ENUM</code></a>.
              </p></li><li class="listitem"><p>
                A column may use any combination of the preceding
                compression types.
</p></li></ul>
</div>
</li><li class="listitem"><p>
            Can be used for fixed-length or dynamic-length rows.
</p></li></ul>
</div>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
<div class="admon-title">
Note
</div>
<p>
          While a compressed table is read only, and you cannot
          therefore update or add rows in the table, DDL (Data
          Definition Language) operations are still valid. For example,
          you may still use <code class="literal">DROP</code> to drop the table,
          and <a class="link" href="sql-syntax.html#truncate-table" title="13.1.34 TRUNCATE TABLE Syntax"><code class="literal">TRUNCATE TABLE</code></a> to empty the
          table.
</p>
</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="myisam-table-problems"></a>16.2.4 MyISAM Table Problems</h3>

</div>

</div>

</div>
<div class="toc">
<dl class="toc"><dt><span class="section"><a href="storage-engines.html#corrupted-myisam-tables">16.2.4.1 Corrupted MyISAM Tables</a></span></dt><dt><span class="section"><a href="storage-engines.html#myisam-table-close">16.2.4.2 Problems from Tables Not Being Closed Properly</a></span></dt></dl>
</div>
<p>
      The file format that MySQL uses to store data has been extensively
      tested, but there are always circumstances that may cause database
      tables to become corrupted. The following discussion describes how
      this can happen and how to handle it.
</p>
<div class="section">

<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="corrupted-myisam-tables"></a>16.2.4.1 Corrupted MyISAM Tables</h4>
</div>
</div>
</div>
<p>
        Even though the <code class="literal">MyISAM</code> table format is very
        reliable (all changes to a table made by an SQL statement are
        written before the statement returns), you can still get
        corrupted tables if any of the following events occur:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            The <a class="link" href="programs.html#mysqld" title="4.3.1 mysqld — The MySQL Server"><span class="command"><strong>mysqld</strong></span></a> process is killed in the
            middle of a write.
          </p></li><li class="listitem"><p>
            An unexpected computer shutdown occurs (for example, the
            computer is turned off).
          </p></li><li class="listitem"><p>
            Hardware failures.
          </p></li><li class="listitem"><p>
            You are using an external program (such as
            <a class="link" href="programs.html#myisamchk" title="4.6.4 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a>) to modify a table that is
            being modified by the server at the same time.
          </p></li><li class="listitem"><p>
            A software bug in the MySQL or <code class="literal">MyISAM</code>
            code.
</p></li></ul>
</div>
<p>
        Typical symptoms of a corrupt table are:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            You get the following error while selecting data from the
            table:
          </p><pre data-lang="none" class="programlisting">
Incorrect key file for table: '...'. Try to repair it
</pre></li><li class="listitem"><p>
            Queries don't find rows in the table or return incomplete
            results.
</p></li></ul>
</div>
<p>
        You can check the health of a <code class="literal">MyISAM</code> table
        using the <a class="link" href="sql-syntax.html#check-table" title="13.7.3.2 CHECK TABLE Syntax"><code class="literal">CHECK TABLE</code></a> statement,
        and repair a corrupted <code class="literal">MyISAM</code> table with
        <a class="link" href="sql-syntax.html#repair-table" title="13.7.3.5 REPAIR TABLE Syntax"><code class="literal">REPAIR TABLE</code></a>. When
        <a class="link" href="programs.html#mysqld" title="4.3.1 mysqld — The MySQL Server"><span class="command"><strong>mysqld</strong></span></a> is not running, you can also check or
        repair a table with the <a class="link" href="programs.html#myisamchk" title="4.6.4 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a> command.
        See <a class="xref" href="sql-syntax.html#check-table" title="13.7.3.2 CHECK TABLE Syntax">Section 13.7.3.2, “CHECK TABLE Syntax”</a>,
        <a class="xref" href="sql-syntax.html#repair-table" title="13.7.3.5 REPAIR TABLE Syntax">Section 13.7.3.5, “REPAIR TABLE Syntax”</a>, and <a class="xref" href="programs.html#myisamchk" title="4.6.4 myisamchk — MyISAM Table-Maintenance Utility">Section 4.6.4, “<span class="command"><strong>myisamchk</strong></span> — MyISAM Table-Maintenance Utility”</a>.
      </p><p>
        If your tables become corrupted frequently, you should try to
        determine why this is happening. The most important thing to
        know is whether the table became corrupted as a result of a
        server crash. You can verify this easily by looking for a recent
        <code class="literal">restarted mysqld</code> message in the error log. If
        there is such a message, it is likely that table corruption is a
        result of the server dying. Otherwise, corruption may have
        occurred during normal operation. This is a bug. You should try
        to create a reproducible test case that demonstrates the
        problem. See <a class="xref" href="error-handling.html#crashing" title="B.5.3.3 What to Do If MySQL Keeps Crashing">Section B.5.3.3, “What to Do If MySQL Keeps Crashing”</a>, and
        <a class="xref" href="extending-mysql.html#porting" title="28.5 Debugging and Porting MySQL">Section 28.5, “Debugging and Porting MySQL”</a>.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="myisam-table-close"></a>16.2.4.2 Problems from Tables Not Being Closed Properly</h4>

</div>

</div>

</div>
<p>
        Each <code class="literal">MyISAM</code> index file
        (<code class="filename">.MYI</code> file) has a counter in the header
        that can be used to check whether a table has been closed
        properly. If you get the following warning from
        <a class="link" href="sql-syntax.html#check-table" title="13.7.3.2 CHECK TABLE Syntax"><code class="literal">CHECK TABLE</code></a> or
        <a class="link" href="programs.html#myisamchk" title="4.6.4 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a>, it means that this counter has
        gone out of sync:
      </p><pre data-lang="none" class="programlisting">
clients are using or haven't closed the table properly
</pre><p>
        This warning doesn't necessarily mean that the table is
        corrupted, but you should at least check the table.
      </p><p>
        The counter works as follows:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            The first time a table is updated in MySQL, a counter in the
            header of the index files is incremented.
          </p></li><li class="listitem"><p>
            The counter is not changed during further updates.
          </p></li><li class="listitem"><p>
            When the last instance of a table is closed (because a
            <a class="link" href="sql-syntax.html#flush-tables"><code class="literal">FLUSH TABLES</code></a> operation was
            performed or because there is no room in the table cache),
            the counter is decremented if the table has been updated at
            any point.
          </p></li><li class="listitem"><p>
            When you repair the table or check the table and it is found
            to be okay, the counter is reset to zero.
          </p></li><li class="listitem"><p>
            To avoid problems with interaction with other processes that
            might check the table, the counter is not decremented on
            close if it was zero.
</p></li></ul>
</div>
<p>
        In other words, the counter can become incorrect only under
        these conditions:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            A <code class="literal">MyISAM</code> table is copied without first
            issuing <a class="link" href="sql-syntax.html#lock-tables" title="13.3.6 LOCK TABLES and UNLOCK TABLES Syntax"><code class="literal">LOCK TABLES</code></a> and
            <a class="link" href="sql-syntax.html#flush-tables"><code class="literal">FLUSH TABLES</code></a>.
          </p></li><li class="listitem"><p>
            MySQL has crashed between an update and the final close.
            (The table may still be okay because MySQL always issues
            writes for everything between each statement.)
          </p></li><li class="listitem"><p>
            A table was modified by <a class="link" href="programs.html#myisamchk" title="4.6.4 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk
            --recover</strong></span></a> or <a class="link" href="programs.html#myisamchk" title="4.6.4 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk
            --update-state</strong></span></a> at the same time that it was in use
            by <a class="link" href="programs.html#mysqld" title="4.3.1 mysqld — The MySQL Server"><span class="command"><strong>mysqld</strong></span></a>.
          </p></li><li class="listitem"><p>
            Multiple <a class="link" href="programs.html#mysqld" title="4.3.1 mysqld — The MySQL Server"><span class="command"><strong>mysqld</strong></span></a> servers are using the
            table and one server performed a <a class="link" href="sql-syntax.html#repair-table" title="13.7.3.5 REPAIR TABLE Syntax"><code class="literal">REPAIR
            TABLE</code></a> or <a class="link" href="sql-syntax.html#check-table" title="13.7.3.2 CHECK TABLE Syntax"><code class="literal">CHECK
            TABLE</code></a> on the table while it was in use by another
            server. In this setup, it is safe to use
            <a class="link" href="sql-syntax.html#check-table" title="13.7.3.2 CHECK TABLE Syntax"><code class="literal">CHECK TABLE</code></a>, although you
            might get the warning from other servers. However,
            <a class="link" href="sql-syntax.html#repair-table" title="13.7.3.5 REPAIR TABLE Syntax"><code class="literal">REPAIR TABLE</code></a> should be
            avoided because when one server replaces the data file with
            a new one, this is not known to the other servers.
          </p><p>
            In general, it is a bad idea to share a data directory among
            multiple servers. See <a class="xref" href="server-administration.html#multiple-servers" title="5.7 Running Multiple MySQL Instances on One Machine">Section 5.7, “Running Multiple MySQL Instances on One Machine”</a>,
            for additional discussion.
</p></li></ul>
</div>

</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="memory-storage-engine"></a>16.3 The MEMORY Storage Engine</h2>

</div>

</div>

</div>
<a class="indexterm" name="idm139899504686272"></a><a class="indexterm" name="idm139899504685232"></a><a class="indexterm" name="idm139899504684160"></a><a class="indexterm" name="idm139899504682672"></a><p>
    The <code class="literal">MEMORY</code> storage engine (formerly known as
    <code class="literal">HEAP</code>) creates special-purpose tables with
    contents that are stored in memory. Because the data is vulnerable
    to crashes, hardware issues, or power outages, only use these tables
    as temporary work areas or read-only caches for data pulled from
    other tables.
</p>
<div class="table">
<a name="idm139899504679056"></a><p class="title"><b>Table 16.4 MEMORY Storage Engine Features</b></p>
<div class="table-contents">
<table frame="box" rules="all" summary="Features supported by the MEMORY storage engine."><col width="60%"><col width="40%"><thead><tr><th scope="col">Feature</th>
<th scope="col">Support</th>
</tr></thead><tbody><tr><td scope="row"><span class="bold"><strong>B-tree indexes</strong></span></td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong>Backup/point-in-time recovery</strong></span> (Implemented in the server, rather than in the storage engine.)</td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong>Cluster database support</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Clustered indexes</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Compressed data</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Data caches</strong></span></td>
<td>N/A</td>
</tr><tr><td scope="row"><span class="bold"><strong>Encrypted data</strong></span> (Implemented in the server via encryption functions. Data-at-rest tablespace encryption is available in MySQL 5.7 and later.)</td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong>Foreign key support</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Full-text search indexes</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Geospatial data type support</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Geospatial indexing support</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Hash indexes</strong></span></td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong>Index caches</strong></span></td>
<td>N/A</td>
</tr><tr><td scope="row"><span class="bold"><strong>Locking granularity</strong></span></td>
<td>Table</td>
</tr><tr><td scope="row"><span class="bold"><strong>MVCC</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Query cache support</strong></span></td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong>Replication support</strong></span> (Implemented in the server, rather than in the storage engine.)</td>
<td>Limited (See the discussion later in this section.)</td>
</tr><tr><td scope="row"><span class="bold"><strong>Storage limits</strong></span></td>
<td>RAM</td>
</tr><tr><td scope="row"><span class="bold"><strong>T-tree indexes</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Transactions</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Update statistics for data dictionary</strong></span></td>
<td>Yes</td>
</tr></tbody></table>
</div>

</div>
<br class="table-break">
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><a class="xref" href="storage-engines.html#memory-storage-engine-compared-cluster" title="When to Use MEMORY or NDB Cluster">When to Use MEMORY or NDB Cluster</a></p></li><li class="listitem"><p><a class="xref" href="storage-engines.html#memory-storage-engine-partitioning" title="Partitioning">Partitioning</a></p></li><li class="listitem"><p><a class="xref" href="storage-engines.html#memory-storage-engine-performance-characteristics" title="Performance Characteristics">Performance Characteristics</a></p></li><li class="listitem"><p><a class="xref" href="storage-engines.html#memory-storage-engine-characteristics-of-memory-tables" title="Characteristics of MEMORY Tables">Characteristics of MEMORY Tables</a></p></li><li class="listitem"><p><a class="xref" href="storage-engines.html#memory-storage-engine-ddl-operations-for-memory-tables" title="DDL Operations for MEMORY Tables">DDL Operations for MEMORY Tables</a></p></li><li class="listitem"><p><a class="xref" href="storage-engines.html#memory-storage-engine-indexes" title="Indexes">Indexes</a></p></li><li class="listitem"><p><a class="xref" href="storage-engines.html#memory-storage-engine-user-created-and-temporary-tables" title="User-Created and Temporary Tables">User-Created and Temporary Tables</a></p></li><li class="listitem"><p><a class="xref" href="storage-engines.html#memory-storage-engine-loading-data" title="Loading Data">Loading Data</a></p></li><li class="listitem"><p><a class="xref" href="storage-engines.html#memory-tables-replication" title="MEMORY Tables and Replication">MEMORY Tables and Replication</a></p></li><li class="listitem"><p><a class="xref" href="storage-engines.html#memory-storage-engine-managing-memory-use" title="Managing Memory Use">Managing Memory Use</a></p></li><li class="listitem"><p><a class="xref" href="storage-engines.html#memory-storage-engine-additional-resources" title="Additional Resources">Additional Resources</a></p></li></ul>
</div>

<div class="simplesect">

<div class="titlepage">
<div>

<div class="simple">
<h3 class="title"><a name="memory-storage-engine-compared-cluster"></a>When to Use MEMORY or NDB Cluster</h3>

</div>

</div>

</div>
<p>
      Developers looking to deploy applications that use the
      <code class="literal">MEMORY</code> storage engine for important, highly
      available, or frequently updated data should consider whether NDB
      Cluster is a better choice. A typical use case for the
      <code class="literal">MEMORY</code> engine involves these characteristics:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          Operations involving transient, non-critical data such as
          session management or caching. When the MySQL server halts or
          restarts, the data in <code class="literal">MEMORY</code> tables is
          lost.
        </p></li><li class="listitem"><p>
          In-memory storage for fast access and low latency. Data volume
          can fit entirely in memory without causing the operating
          system to swap out virtual memory pages.
        </p></li><li class="listitem"><p>
          A read-only or read-mostly data access pattern (limited
          updates).
</p></li></ul>
</div>
<p>
      NDB Cluster offers the same features as the
      <code class="literal">MEMORY</code> engine with higher performance levels,
      and provides additional features not available with
      <code class="literal">MEMORY</code>:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          Row-level locking and multiple-thread operation for low
          contention between clients.
        </p></li><li class="listitem"><p>
          Scalability even with statement mixes that include writes.
        </p></li><li class="listitem"><p>
          Optional disk-backed operation for data durability.
        </p></li><li class="listitem"><p>
          Shared-nothing architecture and multiple-host operation with
          no single point of failure, enabling 99.999% availability.
        </p></li><li class="listitem"><p>
          Automatic data distribution across nodes; application
          developers need not craft custom sharding or partitioning
          solutions.
        </p></li><li class="listitem"><p>
          Support for variable-length data types (including
          <a class="link" href="data-types.html#blob" title="11.4.3 The BLOB and TEXT Types"><code class="literal">BLOB</code></a> and
          <a class="link" href="data-types.html#blob" title="11.4.3 The BLOB and TEXT Types"><code class="literal">TEXT</code></a>) not supported by
          <code class="literal">MEMORY</code>.
</p></li></ul>
</div>

</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h3 class="title"><a name="memory-storage-engine-partitioning"></a>Partitioning</h3>

</div>

</div>

</div>
<p>
      <code class="literal">MEMORY</code> tables cannot be partitioned.
</p>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h3 class="title"><a name="memory-storage-engine-performance-characteristics"></a>Performance Characteristics</h3>

</div>

</div>

</div>
<p>
      <code class="literal">MEMORY</code> performance is constrained by contention
      resulting from single-thread execution and table lock overhead
      when processing updates. This limits scalability when load
      increases, particularly for statement mixes that include writes.
    </p><p>
      Despite the in-memory processing for <code class="literal">MEMORY</code>
      tables, they are not necessarily faster than
      <a class="link" href="innodb-storage-engine.html" title="Chapter 15 The InnoDB Storage Engine"><code class="literal">InnoDB</code></a> tables on a busy server, for
      general-purpose queries, or under a read/write workload. In
      particular, the table locking involved with performing updates can
      slow down concurrent usage of <code class="literal">MEMORY</code> tables
      from multiple sessions.
    </p><p>
      Depending on the kinds of queries performed on a
      <code class="literal">MEMORY</code> table, you might create indexes as
      either the default hash data structure (for looking up single
      values based on a unique key), or a general-purpose B-tree data
      structure (for all kinds of queries involving equality,
      inequality, or range operators such as less than or greater than).
      The following sections illustrate the syntax for creating both
      kinds of indexes. A common performance issue is using the default
      hash indexes in workloads where B-tree indexes are more efficient.
</p>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h3 class="title"><a name="memory-storage-engine-characteristics-of-memory-tables"></a>Characteristics of MEMORY Tables</h3>

</div>

</div>

</div>
<p>
      The <code class="literal">MEMORY</code> storage engine does not create any
      files on disk. The table definition is stored in the MySQL data
      dictionary.
    </p><p>
      <code class="literal">MEMORY</code> tables have the following
      characteristics:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          Space for <code class="literal">MEMORY</code> tables is allocated in
          small blocks. Tables use 100% dynamic hashing for inserts. No
          overflow area or extra key space is needed. No extra space is
          needed for free lists. Deleted rows are put in a linked list
          and are reused when you insert new data into the table.
          <code class="literal">MEMORY</code> tables also have none of the
          problems commonly associated with deletes plus inserts in
          hashed tables.
        </p></li><li class="listitem"><p>
          <code class="literal">MEMORY</code> tables use a fixed-length
          row-storage format. Variable-length types such as
          <a class="link" href="data-types.html#char" title="11.4.1 The CHAR and VARCHAR Types"><code class="literal">VARCHAR</code></a> are stored using a
          fixed length.
        </p></li><li class="listitem"><p>
          <code class="literal">MEMORY</code> tables cannot contain
          <a class="link" href="data-types.html#blob" title="11.4.3 The BLOB and TEXT Types"><code class="literal">BLOB</code></a> or
          <a class="link" href="data-types.html#blob" title="11.4.3 The BLOB and TEXT Types"><code class="literal">TEXT</code></a> columns.
        </p></li><li class="listitem"><p>
          <code class="literal">MEMORY</code> includes support for
          <code class="literal">AUTO_INCREMENT</code> columns.
        </p></li><li class="listitem"><p>
          Non-<code class="literal">TEMPORARY</code> <code class="literal">MEMORY</code>
          tables are shared among all clients, just like any other
          non-<code class="literal">TEMPORARY</code> table.
</p></li></ul>
</div>

</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h3 class="title"><a name="memory-storage-engine-ddl-operations-for-memory-tables"></a>DDL Operations for MEMORY Tables</h3>

</div>

</div>

</div>
<p>
      To create a <code class="literal">MEMORY</code> table, specify the clause
      <code class="literal">ENGINE=MEMORY</code> on the
      <a class="link" href="sql-syntax.html#create-table" title="13.1.18 CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a> statement.
    </p><pre data-lang="sql" class="programlisting">
CREATE TABLE t (i INT) ENGINE = MEMORY;
</pre><p>
      As indicated by the engine name, <code class="literal">MEMORY</code> tables
      are stored in memory. They use hash indexes by default, which
      makes them very fast for single-value lookups, and very useful for
      creating temporary tables. However, when the server shuts down,
      all rows stored in <code class="literal">MEMORY</code> tables are lost. The
      tables themselves continue to exist because their definitions are
      stored in the MySQL data dictionary, but they are empty when the
      server restarts.
    </p><p>
      This example shows how you might create, use, and remove a
      <code class="literal">MEMORY</code> table:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>CREATE TABLE test ENGINE=MEMORY</code></strong>
    -&gt;     <strong class="userinput"><code>SELECT ip,SUM(downloads) AS down</code></strong>
    -&gt;     <strong class="userinput"><code>FROM log_table GROUP BY ip;</code></strong>
mysql&gt; <strong class="userinput"><code>SELECT COUNT(ip),AVG(down) FROM test;</code></strong>
mysql&gt; <strong class="userinput"><code>DROP TABLE test;</code></strong>
</pre><p>
      The maximum size of <code class="literal">MEMORY</code> tables is limited by
      the <a class="link" href="server-administration.html#sysvar_max_heap_table_size"><code class="literal">max_heap_table_size</code></a> system
      variable, which has a default value of 16MB. To enforce different
      size limits for <code class="literal">MEMORY</code> tables, change the value
      of this variable. The value in effect for
      <a class="link" href="sql-syntax.html#create-table" title="13.1.18 CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a>, or a subsequent
      <a class="link" href="sql-syntax.html#alter-table" title="13.1.8 ALTER TABLE Syntax"><code class="literal">ALTER TABLE</code></a> or
      <a class="link" href="sql-syntax.html#truncate-table" title="13.1.34 TRUNCATE TABLE Syntax"><code class="literal">TRUNCATE TABLE</code></a>, is the value used
      for the life of the table. A server restart also sets the maximum
      size of existing <code class="literal">MEMORY</code> tables to the global
      <a class="link" href="server-administration.html#sysvar_max_heap_table_size"><code class="literal">max_heap_table_size</code></a> value. You
      can set the size for individual tables as described later in this
      section.
</p>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h3 class="title"><a name="memory-storage-engine-indexes"></a>Indexes</h3>

</div>

</div>

</div>
<p>
      The <code class="literal">MEMORY</code> storage engine supports both
      <code class="literal">HASH</code> and <code class="literal">BTREE</code> indexes. You
      can specify one or the other for a given index by adding a
      <code class="literal">USING</code> clause as shown here:
    </p><pre data-lang="sql" class="programlisting">
CREATE TABLE lookup
    (id INT, INDEX USING HASH (id))
    ENGINE = MEMORY;
CREATE TABLE lookup
    (id INT, INDEX USING BTREE (id))
    ENGINE = MEMORY;
</pre><p>
      For general characteristics of B-tree and hash indexes, see
      <a class="xref" href="optimization.html#mysql-indexes" title="8.3.1 How MySQL Uses Indexes">Section 8.3.1, “How MySQL Uses Indexes”</a>.
    </p><p>
      <code class="literal">MEMORY</code> tables can have up to 64 indexes per
      table, 16 columns per index and a maximum key length of 3072
      bytes.
    </p><p>
      If a <code class="literal">MEMORY</code> table hash index has a high degree
      of key duplication (many index entries containing the same value),
      updates to the table that affect key values and all deletes are
      significantly slower. The degree of this slowdown is proportional
      to the degree of duplication (or, inversely proportional to the
      index cardinality). You can use a <code class="literal">BTREE</code> index
      to avoid this problem.
    </p><p>
      <code class="literal">MEMORY</code> tables can have nonunique keys. (This is
      an uncommon feature for implementations of hash indexes.)
    </p><p>
      Columns that are indexed can contain <code class="literal">NULL</code>
      values.
</p>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h3 class="title"><a name="memory-storage-engine-user-created-and-temporary-tables"></a>User-Created and Temporary Tables</h3>

</div>

</div>

</div>
<p>
      <code class="literal">MEMORY</code> table contents are stored in memory,
      which is a property that <code class="literal">MEMORY</code> tables share
      with internal temporary tables that the server creates on the fly
      while processing queries. However, the two types of tables differ
      in that <code class="literal">MEMORY</code> tables are not subject to
      storage conversion, whereas internal temporary tables are:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          If an internal temporary table becomes too large, the server
          automatically converts it to on-disk storage, as described in
          <a class="xref" href="optimization.html#internal-temporary-tables" title="8.4.4 Internal Temporary Table Use in MySQL">Section 8.4.4, “Internal Temporary Table Use in MySQL”</a>.
        </p></li><li class="listitem"><p>
          User-created <code class="literal">MEMORY</code> tables are never
          converted to disk tables.
</p></li></ul>
</div>

</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h3 class="title"><a name="memory-storage-engine-loading-data"></a>Loading Data</h3>

</div>

</div>

</div>
<p>
      To populate a <code class="literal">MEMORY</code> table when the MySQL
      server starts, you can use the
      <a class="link" href="server-administration.html#option_mysqld_init-file"><code class="option">--init-file</code></a> option. For example,
      you can put statements such as
      <a class="link" href="sql-syntax.html#insert-select" title="13.2.6.1 INSERT ... SELECT Syntax"><code class="literal">INSERT INTO ...
      SELECT</code></a> or
      <a class="link" href="sql-syntax.html#load-data" title="13.2.7 LOAD DATA INFILE Syntax"><code class="literal">LOAD DATA
      INFILE</code></a> into this file to load the table from a
      persistent data source. See <a class="xref" href="server-administration.html#server-options" title="5.1.4 Server Command Options">Section 5.1.4, “Server Command Options”</a>, and
      <a class="xref" href="sql-syntax.html#load-data" title="13.2.7 LOAD DATA INFILE Syntax">Section 13.2.7, “LOAD DATA INFILE Syntax”</a>.
</p>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h3 class="title"><a name="memory-tables-replication"></a>MEMORY Tables and Replication</h3>

</div>

</div>

</div>
<p>
      A server's <code class="literal">MEMORY</code> tables become empty when it
      is shut down and restarted. If the server is a replication master,
      its slaves are not aware that these tables have become empty, so
      you see out-of-date content if you select data from the tables on
      the slaves. To synchronize master and slave
      <code class="literal">MEMORY</code> tables, when a <code class="literal">MEMORY</code>
      table is used on a master for the first time since it was started,
      a <a class="link" href="sql-syntax.html#delete" title="13.2.2 DELETE Syntax"><code class="literal">DELETE</code></a> statement is written to
      the master's binary log, to empty the table on the slaves also.
      The slave still has outdated data in the table during the interval
      between the master's restart and its first use of the table. To
      avoid this interval when a direct query to the slave could return
      stale data, use the <a class="link" href="server-administration.html#option_mysqld_init-file"><code class="option">--init-file</code></a>
      option to populate the <code class="literal">MEMORY</code> table on the
      master at startup.
</p>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h3 class="title"><a name="memory-storage-engine-managing-memory-use"></a>Managing Memory Use</h3>

</div>

</div>

</div>
<p>
      The server needs sufficient memory to maintain all
      <code class="literal">MEMORY</code> tables that are in use at the same time.
    </p><p>
      Memory is not reclaimed if you delete individual rows from a
      <code class="literal">MEMORY</code> table. Memory is reclaimed only when the
      entire table is deleted. Memory that was previously used for
      deleted rows is re-used for new rows within the same table. To
      free all the memory used by a <code class="literal">MEMORY</code> table when
      you no longer require its contents, execute
      <a class="link" href="sql-syntax.html#delete" title="13.2.2 DELETE Syntax"><code class="literal">DELETE</code></a> or
      <a class="link" href="sql-syntax.html#truncate-table" title="13.1.34 TRUNCATE TABLE Syntax"><code class="literal">TRUNCATE TABLE</code></a> to remove all rows,
      or remove the table altogether using <a class="link" href="sql-syntax.html#drop-table" title="13.1.29 DROP TABLE Syntax"><code class="literal">DROP
      TABLE</code></a>. To free up the memory used by deleted rows, use
      <code class="literal">ALTER TABLE ENGINE=MEMORY</code> to force a table
      rebuild.
    </p><p>
      The memory needed for one row in a <code class="literal">MEMORY</code> table
      is calculated using the following expression:
    </p><pre data-lang="sql" class="programlisting">
SUM_OVER_ALL_BTREE_KEYS(<em class="replaceable"><code>max_length_of_key</code></em> + sizeof(char*) * 4)
+ SUM_OVER_ALL_HASH_KEYS(sizeof(char*) * 2)
+ ALIGN(<em class="replaceable"><code>length_of_row</code></em>+1, sizeof(char*))
</pre><p>
      <code class="literal">ALIGN()</code> represents a round-up factor to cause
      the row length to be an exact multiple of the
      <code class="literal">char</code> pointer size.
      <code class="literal">sizeof(char*)</code> is 4 on 32-bit machines and 8 on
      64-bit machines.
    </p><p>
      As mentioned earlier, the
      <a class="link" href="server-administration.html#sysvar_max_heap_table_size"><code class="literal">max_heap_table_size</code></a> system
      variable sets the limit on the maximum size of
      <code class="literal">MEMORY</code> tables. To control the maximum size for
      individual tables, set the session value of this variable before
      creating each table. (Do not change the global
      <a class="link" href="server-administration.html#sysvar_max_heap_table_size"><code class="literal">max_heap_table_size</code></a> value unless
      you intend the value to be used for <code class="literal">MEMORY</code>
      tables created by all clients.) The following example creates two
      <code class="literal">MEMORY</code> tables, with a maximum size of 1MB and
      2MB, respectively:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SET max_heap_table_size = 1024*1024;</code></strong>
Query OK, 0 rows affected (0.00 sec)

mysql&gt; <strong class="userinput"><code>CREATE TABLE t1 (id INT, UNIQUE(id)) ENGINE = MEMORY;</code></strong>
Query OK, 0 rows affected (0.01 sec)

mysql&gt; <strong class="userinput"><code>SET max_heap_table_size = 1024*1024*2;</code></strong>
Query OK, 0 rows affected (0.00 sec)

mysql&gt; <strong class="userinput"><code>CREATE TABLE t2 (id INT, UNIQUE(id)) ENGINE = MEMORY;</code></strong>
Query OK, 0 rows affected (0.00 sec)
</pre><p>
      Both tables revert to the server's global
      <a class="link" href="server-administration.html#sysvar_max_heap_table_size"><code class="literal">max_heap_table_size</code></a> value if the
      server restarts.
    </p><p>
      You can also specify a <code class="literal">MAX_ROWS</code> table option in
      <a class="link" href="sql-syntax.html#create-table" title="13.1.18 CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a> statements for
      <code class="literal">MEMORY</code> tables to provide a hint about the
      number of rows you plan to store in them. This does not enable the
      table to grow beyond the
      <a class="link" href="server-administration.html#sysvar_max_heap_table_size"><code class="literal">max_heap_table_size</code></a> value, which
      still acts as a constraint on maximum table size. For maximum
      flexibility in being able to use <code class="literal">MAX_ROWS</code>, set
      <a class="link" href="server-administration.html#sysvar_max_heap_table_size"><code class="literal">max_heap_table_size</code></a> at least as
      high as the value to which you want each <code class="literal">MEMORY</code>
      table to be able to grow.
</p>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h3 class="title"><a name="memory-storage-engine-additional-resources"></a>Additional Resources</h3>

</div>

</div>

</div>
<p>
      A forum dedicated to the <code class="literal">MEMORY</code> storage engine
      is available at <a class="ulink" href="http://forums.mysql.com/list.php?92" target="_top">http://forums.mysql.com/list.php?92</a>.
</p>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="csv-storage-engine"></a>16.4 The CSV Storage Engine</h2>

</div>

</div>

</div>
<div class="toc">
<dl class="toc"><dt><span class="section"><a href="storage-engines.html#se-csv-repair">16.4.1 Repairing and Checking CSV Tables</a></span></dt><dt><span class="section"><a href="storage-engines.html#se-csv-limitations">16.4.2 CSV Limitations</a></span></dt></dl>
</div>
<a class="indexterm" name="idm139899504460336"></a><a class="indexterm" name="idm139899504459264"></a><p>
    The <code class="literal">CSV</code> storage engine stores data in text files
    using comma-separated values format.
  </p><p>
    The <code class="literal">CSV</code> storage engine is always compiled into
    the MySQL server.
  </p><p>
    To examine the source for the <code class="literal">CSV</code> engine, look in
    the <code class="filename">storage/csv</code> directory of a MySQL source
    distribution.
  </p><p>
    When you create a <code class="literal">CSV</code> table, the server creates a
    data file. The data file name begins with the table name and has a
    <code class="filename">.CSV</code> extension. The data file is a plain text
    file. When you store data into the table, the storage engine saves
    it into the data file in comma-separated values format.
  </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>CREATE TABLE test (i INT NOT NULL, c CHAR(10) NOT NULL)</code></strong>
    -&gt; <strong class="userinput"><code>ENGINE = CSV;</code></strong>
Query OK, 0 rows affected (0.12 sec)

mysql&gt; <strong class="userinput"><code>INSERT INTO test VALUES(1,'record one'),(2,'record two');</code></strong>
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql&gt; <strong class="userinput"><code>SELECT * FROM test;</code></strong>
+------+------------+
| i    | c          |
+------+------------+
|    1 | record one |
|    2 | record two |
+------+------------+
2 rows in set (0.00 sec)
</pre><p>
    Creating a CSV table also creates a corresponding Metafile that
    stores the state of the table and the number of rows that exist in
    the table. The name of this file is the same as the name of the
    table with the extension <code class="filename">CSM</code>.
  </p><p>
    If you examine the <code class="filename">test.CSV</code> file in the
    database directory created by executing the preceding statements,
    its contents should look like this:
  </p><pre data-lang="none" class="programlisting">
"1","record one"
"2","record two"
</pre><p>
    This format can be read, and even written, by spreadsheet
    applications such as Microsoft Excel or StarOffice Calc.
</p>
<div class="section">

<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="se-csv-repair"></a>16.4.1 Repairing and Checking CSV Tables</h3>
</div>
</div>
</div>
<p>
      The CSV storage engines supports the <code class="literal">CHECK</code> and
      <code class="literal">REPAIR</code> statements to verify and if possible
      repair a damaged CSV table.
    </p><p>
      When running the <code class="literal">CHECK</code> statement, the CSV file
      will be checked for validity by looking for the correct field
      separators, escaped fields (matching or missing quotation marks),
      the correct number of fields compared to the table definition and
      the existence of a corresponding CSV metafile. The first invalid
      row discovered will report an error. Checking a valid table
      produces output like that shown below:
    </p><pre data-lang="sql" class="programlisting">mysql&gt; <strong class="userinput"><code>check table csvtest;</code></strong>
+--------------+-------+----------+----------+
| Table        | Op    | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| test.csvtest | check | status   | OK       |
+--------------+-------+----------+----------+
1 row in set (0.00 sec)</pre><p>
      A check on a corrupted table returns a fault:
    </p><pre data-lang="sql" class="programlisting">mysql&gt; <strong class="userinput"><code>check table csvtest;</code></strong>
+--------------+-------+----------+----------+
| Table        | Op    | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| test.csvtest | check | error    | Corrupt  |
+--------------+-------+----------+----------+
1 row in set (0.01 sec)</pre><p>
      If the check fails, the table is marked as crashed (corrupt). Once
      a table has been marked as corrupt, it is automatically repaired
      when you next run <code class="literal">CHECK</code> or execute a
      <a class="link" href="sql-syntax.html#select" title="13.2.10 SELECT Syntax"><code class="literal">SELECT</code></a> statement. The corresponding
      corrupt status and new status will be displayed when running
      <code class="literal">CHECK</code>:
    </p><pre data-lang="sql" class="programlisting">mysql&gt; <strong class="userinput"><code>check table csvtest;</code></strong>
+--------------+-------+----------+----------------------------+
| Table        | Op    | Msg_type | Msg_text                   |
+--------------+-------+----------+----------------------------+
| test.csvtest | check | warning  | Table is marked as crashed |
| test.csvtest | check | status   | OK                         |
+--------------+-------+----------+----------------------------+
2 rows in set (0.08 sec)</pre><p>
      To repair a table you can use <code class="literal">REPAIR</code>, this
      copies as many valid rows from the existing CSV data as possible,
      and then replaces the existing CSV file with the recovered rows.
      Any rows beyond the corrupted data are lost.
    </p><pre data-lang="sql" class="programlisting">mysql&gt; <strong class="userinput"><code>repair table csvtest;</code></strong>
+--------------+--------+----------+----------+
| Table        | Op     | Msg_type | Msg_text |
+--------------+--------+----------+----------+
| test.csvtest | repair | status   | OK       |
+--------------+--------+----------+----------+
1 row in set (0.02 sec)</pre>
<div class="warning" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Warning
</div>
<p>
        During repair, only the rows from the CSV file up to the first
        damaged row are copied to the new table. All other rows from the
        first damaged row to the end of the table are removed, even
        valid rows.
</p>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="se-csv-limitations"></a>16.4.2 CSV Limitations</h3>

</div>

</div>

</div>
<p>
      The <code class="literal">CSV</code> storage engine does not support
      indexing.
    </p><p>
      The <code class="literal">CSV</code> storage engine does not support
      partitioning.
    </p><p>
      All tables that you create using the <code class="literal">CSV</code>
      storage engine must have the <code class="literal">NOT NULL</code> attribute
      on all columns. However, for backward compatibility, you can
      continue to use tables with nullable columns that were created in
      previous MySQL releases. (Bug #32050)
</p>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="archive-storage-engine"></a>16.5 The ARCHIVE Storage Engine</h2>

</div>

</div>

</div>
<a class="indexterm" name="idm139899504417520"></a><a class="indexterm" name="idm139899504416480"></a><p>
    The <code class="literal">ARCHIVE</code> storage engine produces
    special-purpose tables that store large amounts of unindexed data in
    a very small footprint.
</p>
<div class="table">
<a name="idm139899504413648"></a><p class="title"><b>Table 16.5 ARCHIVE Storage Engine Features</b></p>
<div class="table-contents">
<table frame="box" rules="all" summary="Features supported by the ARCHIVE storage engine."><col width="60%"><col width="40%"><thead><tr><th scope="col">Feature</th>
<th scope="col">Support</th>
</tr></thead><tbody><tr><td scope="row"><span class="bold"><strong>B-tree indexes</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Backup/point-in-time recovery</strong></span> (Implemented in the server, rather than in the storage engine.)</td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong>Cluster database support</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Clustered indexes</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Compressed data</strong></span></td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong>Data caches</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Encrypted data</strong></span> (Implemented in the server via encryption functions. Data-at-rest tablespace encryption is available in MySQL 5.7 and later.)</td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong>Foreign key support</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Full-text search indexes</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Geospatial data type support</strong></span></td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong>Geospatial indexing support</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Hash indexes</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Index caches</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Locking granularity</strong></span></td>
<td>Row</td>
</tr><tr><td scope="row"><span class="bold"><strong>MVCC</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Query cache support</strong></span></td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong>Replication support</strong></span> (Implemented in the server, rather than in the storage engine.)</td>
<td>Yes</td>
</tr><tr><td scope="row"><span class="bold"><strong>Storage limits</strong></span></td>
<td>None</td>
</tr><tr><td scope="row"><span class="bold"><strong>T-tree indexes</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Transactions</strong></span></td>
<td>No</td>
</tr><tr><td scope="row"><span class="bold"><strong>Update statistics for data dictionary</strong></span></td>
<td>Yes</td>
</tr></tbody></table>
</div>

</div>
<br class="table-break"><p>
    The <code class="literal">ARCHIVE</code> storage engine is included in MySQL
    binary distributions. To enable this storage engine if you build
    MySQL from source, invoke <span class="command"><strong>CMake</strong></span> with the
    <a class="link" href="installing.html#option_cmake_storage_engine_options" title="Storage Engine Options"><code class="option">-DWITH_ARCHIVE_STORAGE_ENGINE</code></a>
    option.
  </p><p>
    To examine the source for the <code class="literal">ARCHIVE</code> engine,
    look in the <code class="filename">storage/archive</code> directory of a
    MySQL source distribution.
  </p><p>
    You can check whether the <code class="literal">ARCHIVE</code> storage engine
    is available with the <a class="link" href="sql-syntax.html#show-engines" title="13.7.6.16 SHOW ENGINES Syntax"><code class="literal">SHOW ENGINES</code></a>
    statement.
  </p><p>
    When you create an <code class="literal">ARCHIVE</code> table, the storage
    engine creates files with names that begin with the table name. The
    data file has an extension of <code class="filename">.ARZ</code>. An
    <code class="filename">.ARN</code> file may appear during optimization
    operations.
  </p><p>
    The <code class="literal">ARCHIVE</code> engine supports
    <a class="link" href="sql-syntax.html#insert" title="13.2.6 INSERT Syntax"><code class="literal">INSERT</code></a>,
    <a class="link" href="sql-syntax.html#replace" title="13.2.9 REPLACE Syntax"><code class="literal">REPLACE</code></a>, and
    <a class="link" href="sql-syntax.html#select" title="13.2.10 SELECT Syntax"><code class="literal">SELECT</code></a>, but not
    <a class="link" href="sql-syntax.html#delete" title="13.2.2 DELETE Syntax"><code class="literal">DELETE</code></a> or
    <a class="link" href="sql-syntax.html#update" title="13.2.12 UPDATE Syntax"><code class="literal">UPDATE</code></a>. It does support
    <code class="literal">ORDER BY</code> operations,
    <a class="link" href="data-types.html#blob" title="11.4.3 The BLOB and TEXT Types"><code class="literal">BLOB</code></a> columns, and basically all but
    spatial data types (see <a class="xref" href="data-types.html#spatial-type-overview" title="11.5.1 Spatial Data Types">Section 11.5.1, “Spatial Data Types”</a>).
    The <code class="literal">ARCHIVE</code> engine uses row-level locking.
  </p><p>
    The <code class="literal">ARCHIVE</code> engine supports the
    <code class="literal">AUTO_INCREMENT</code> column attribute. The
    <code class="literal">AUTO_INCREMENT</code> column can have either a unique or
    nonunique index. Attempting to create an index on any other column
    results in an error. The <code class="literal">ARCHIVE</code> engine also
    supports the <code class="literal">AUTO_INCREMENT</code> table option in
    <a class="link" href="sql-syntax.html#create-table" title="13.1.18 CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a> statements to specify
    the initial sequence value for a new table or reset the sequence
    value for an existing table, respectively.
  </p><p>
    <code class="literal">ARCHIVE</code> does not support inserting a value into
    an <code class="literal">AUTO_INCREMENT</code> column less than the current
    maximum column value. Attempts to do so result in an
    <a class="link" href="error-handling.html#error_er_dup_key"><code class="literal">ER_DUP_KEY</code></a> error.
  </p><p>
    The <code class="literal">ARCHIVE</code> engine ignores
    <a class="link" href="data-types.html#blob" title="11.4.3 The BLOB and TEXT Types"><code class="literal">BLOB</code></a> columns if they are not
    requested and scans past them while reading.
  </p><p>
    The <code class="literal">ARCHIVE</code> storage engine does not support
    partitioning.
  </p><p>
    <span class="bold"><strong>Storage:</strong></span> Rows are compressed as
    they are inserted. The <code class="literal">ARCHIVE</code> engine uses
    <code class="literal">zlib</code> lossless data compression (see
    <a class="ulink" href="http://www.zlib.net/" target="_top">http://www.zlib.net/</a>). You can use
    <a class="link" href="sql-syntax.html#optimize-table" title="13.7.3.4 OPTIMIZE TABLE Syntax"><code class="literal">OPTIMIZE TABLE</code></a> to analyze the table
    and pack it into a smaller format (for a reason to use
    <a class="link" href="sql-syntax.html#optimize-table" title="13.7.3.4 OPTIMIZE TABLE Syntax"><code class="literal">OPTIMIZE TABLE</code></a>, see later in this
    section). The engine also supports <a class="link" href="sql-syntax.html#check-table" title="13.7.3.2 CHECK TABLE Syntax"><code class="literal">CHECK
    TABLE</code></a>. There are several types of insertions that are
    used:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
        An <a class="link" href="sql-syntax.html#insert" title="13.2.6 INSERT Syntax"><code class="literal">INSERT</code></a> statement just pushes
        rows into a compression buffer, and that buffer flushes as
        necessary. The insertion into the buffer is protected by a lock.
        A <a class="link" href="sql-syntax.html#select" title="13.2.10 SELECT Syntax"><code class="literal">SELECT</code></a> forces a flush to occur.
      </p></li><li class="listitem"><p>
        A bulk insert is visible only after it completes, unless other
        inserts occur at the same time, in which case it can be seen
        partially. A <a class="link" href="sql-syntax.html#select" title="13.2.10 SELECT Syntax"><code class="literal">SELECT</code></a> never causes
        a flush of a bulk insert unless a normal insert occurs while it
        is loading.
</p></li></ul>
</div>
<p>
    <span class="bold"><strong>Retrieval</strong></span>: On retrieval, rows are
    uncompressed on demand; there is no row cache. A
    <a class="link" href="sql-syntax.html#select" title="13.2.10 SELECT Syntax"><code class="literal">SELECT</code></a> operation performs a complete
    table scan: When a <a class="link" href="sql-syntax.html#select" title="13.2.10 SELECT Syntax"><code class="literal">SELECT</code></a> occurs, it
    finds out how many rows are currently available and reads that
    number of rows. <a class="link" href="sql-syntax.html#select" title="13.2.10 SELECT Syntax"><code class="literal">SELECT</code></a> is performed
    as a consistent read. Note that lots of
    <a class="link" href="sql-syntax.html#select" title="13.2.10 SELECT Syntax"><code class="literal">SELECT</code></a> statements during insertion
    can deteriorate the compression, unless only bulk or delayed inserts
    are used. To achieve better compression, you can use
    <a class="link" href="sql-syntax.html#optimize-table" title="13.7.3.4 OPTIMIZE TABLE Syntax"><code class="literal">OPTIMIZE TABLE</code></a> or
    <a class="link" href="sql-syntax.html#repair-table" title="13.7.3.5 REPAIR TABLE Syntax"><code class="literal">REPAIR TABLE</code></a>. The number of rows in
    <code class="literal">ARCHIVE</code> tables reported by
    <a class="link" href="sql-syntax.html#show-table-status" title="13.7.6.36 SHOW TABLE STATUS Syntax"><code class="literal">SHOW TABLE STATUS</code></a> is always accurate.
    See <a class="xref" href="sql-syntax.html#optimize-table" title="13.7.3.4 OPTIMIZE TABLE Syntax">Section 13.7.3.4, “OPTIMIZE TABLE Syntax”</a>,
    <a class="xref" href="sql-syntax.html#repair-table" title="13.7.3.5 REPAIR TABLE Syntax">Section 13.7.3.5, “REPAIR TABLE Syntax”</a>, and
    <a class="xref" href="sql-syntax.html#show-table-status" title="13.7.6.36 SHOW TABLE STATUS Syntax">Section 13.7.6.36, “SHOW TABLE STATUS Syntax”</a>.
</p>
<h3><a name="idm139899504291616"></a>Additional Resources</h3>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
        A forum dedicated to the <code class="literal">ARCHIVE</code> storage
        engine is available at <a class="ulink" href="http://forums.mysql.com/list.php?112" target="_top">http://forums.mysql.com/list.php?112</a>.
</p></li></ul>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="blackhole-storage-engine"></a>16.6 The BLACKHOLE Storage Engine</h2>

</div>

</div>

</div>
<a class="indexterm" name="idm139899504287120"></a><a class="indexterm" name="idm139899504286032"></a><p>
    The <code class="literal">BLACKHOLE</code> storage engine acts as a
    <span class="quote">“<span class="quote">black hole</span>”</span> that accepts data but throws it away and
    does not store it. Retrievals always return an empty result:
  </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>CREATE TABLE test(i INT, c CHAR(10)) ENGINE = BLACKHOLE;</code></strong>
Query OK, 0 rows affected (0.03 sec)

mysql&gt; <strong class="userinput"><code>INSERT INTO test VALUES(1,'record one'),(2,'record two');</code></strong>
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql&gt; <strong class="userinput"><code>SELECT * FROM test;</code></strong>
Empty set (0.00 sec)
</pre><p>
    To enable the <code class="literal">BLACKHOLE</code> storage engine if you
    build MySQL from source, invoke <span class="command"><strong>CMake</strong></span> with the
    <a class="link" href="installing.html#option_cmake_storage_engine_options" title="Storage Engine Options"><code class="option">-DWITH_BLACKHOLE_STORAGE_ENGINE</code></a>
    option.
  </p><p>
    To examine the source for the <code class="literal">BLACKHOLE</code> engine,
    look in the <code class="filename">sql</code> directory of a MySQL source
    distribution.
  </p><p>
    When you create a <code class="literal">BLACKHOLE</code> table, the server
    creates the table definition in the global data dictionary. There
    are no files associated with the table.
  </p><p>
    The <code class="literal">BLACKHOLE</code> storage engine supports all kinds
    of indexes. That is, you can include index declarations in the table
    definition.
  </p><p>
    The <code class="literal">BLACKHOLE</code> storage engine does not support
    partitioning.
  </p><p>
    You can check whether the <code class="literal">BLACKHOLE</code> storage
    engine is available with the <a class="link" href="sql-syntax.html#show-engines" title="13.7.6.16 SHOW ENGINES Syntax"><code class="literal">SHOW
    ENGINES</code></a> statement.
  </p><p>
    Inserts into a <code class="literal">BLACKHOLE</code> table do not store any
    data, but if statement based binary logging is enabled, the SQL
    statements are logged and replicated to slave servers. This can be
    useful as a repeater or filter mechanism.
  </p><p>
    Suppose that your application requires slave-side filtering rules,
    but transferring all binary log data to the slave first results in
    too much traffic. In such a case, it is possible to set up on the
    master host a <span class="quote">“<span class="quote">dummy</span>”</span> slave process whose default
    storage engine is <code class="literal">BLACKHOLE</code>, depicted as follows:
</p>
<div class="figure">
<a name="idm139899504265376"></a><p class="title"><b>Figure 16.1 Replication using BLACKHOLE for Filtering</b></p>
<div class="figure-contents">

<div class="mediaobject">
<img src="images/blackhole-1.png" width="520" height="245" alt="Replication using BLACKHOLE for filtering">
</div>

</div>

</div>
<br class="figure-break"><p>
    The master writes to its binary log. The <span class="quote">“<span class="quote">dummy</span>”</span>
    <a class="link" href="programs.html#mysqld" title="4.3.1 mysqld — The MySQL Server"><span class="command"><strong>mysqld</strong></span></a> process acts as a slave, applying the
    desired combination of <code class="literal">replicate-do-*</code> and
    <code class="literal">replicate-ignore-*</code> rules, and writes a new,
    filtered binary log of its own. (See
    <a class="xref" href="replication.html#replication-options" title="17.1.6 Replication and Binary Logging Options and Variables">Section 17.1.6, “Replication and Binary Logging Options and Variables”</a>.) This filtered log is
    provided to the slave.
  </p><p>
    The dummy process does not actually store any data, so there is
    little processing overhead incurred by running the additional
    <a class="link" href="programs.html#mysqld" title="4.3.1 mysqld — The MySQL Server"><span class="command"><strong>mysqld</strong></span></a> process on the replication master host.
    This type of setup can be repeated with additional replication
    slaves.
  </p><p>
    <a class="link" href="sql-syntax.html#insert" title="13.2.6 INSERT Syntax"><code class="literal">INSERT</code></a> triggers for
    <code class="literal">BLACKHOLE</code> tables work as expected. However,
    because the <code class="literal">BLACKHOLE</code> table does not actually
    store any data, <a class="link" href="sql-syntax.html#update" title="13.2.12 UPDATE Syntax"><code class="literal">UPDATE</code></a> and
    <a class="link" href="sql-syntax.html#delete" title="13.2.2 DELETE Syntax"><code class="literal">DELETE</code></a> triggers are not activated:
    The <code class="literal">FOR EACH ROW</code> clause in the trigger definition
    does not apply because there are no rows.
  </p><p>
    Other possible uses for the <code class="literal">BLACKHOLE</code> storage
    engine include:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
        Verification of dump file syntax.
      </p></li><li class="listitem"><p>
        Measurement of the overhead from binary logging, by comparing
        performance using <code class="literal">BLACKHOLE</code> with and without
        binary logging enabled.
      </p></li><li class="listitem"><p>
        <code class="literal">BLACKHOLE</code> is essentially a
        <span class="quote">“<span class="quote">no-op</span>”</span> storage engine, so it could be used for
        finding performance bottlenecks not related to the storage
        engine itself.
</p></li></ul>
</div>
<p>
    The <code class="literal">BLACKHOLE</code> engine is transaction-aware, in the
    sense that committed transactions are written to the binary log and
    rolled-back transactions are not.
  </p><p>
    <span class="bold"><strong>Blackhole Engine and Auto Increment
    Columns</strong></span>
  </p><p>
    The Blackhole engine is a no-op engine. Any operations performed on
    a table using Blackhole will have no effect. This should be born in
    mind when considering the behavior of primary key columns that auto
    increment. The engine will not automatically increment field values,
    and does not retain auto increment field state. This has important
    implications in replication.
  </p><p>
    Consider the following replication scenario where all three of the
    following conditions apply:
</p>
<div class="orderedlist">
<ol class="orderedlist" type="1"><li class="listitem"><p>
        On a master server there is a blackhole table with an auto
        increment field that is a primary key.
      </p></li><li class="listitem"><p>
        On a slave the same table exists but using the MyISAM engine.
      </p></li><li class="listitem"><p>
        Inserts are performed into the master's table without explicitly
        setting the auto increment value in the
        <code class="literal">INSERT</code> statement itself or through using a
        <code class="literal">SET INSERT_ID</code> statement.
</p></li></ol>
</div>
<p>
    In this scenario replication will fail with a duplicate entry error
    on the primary key column.
  </p><p>
    In statement based replication, the value of
    <code class="literal">INSERT_ID</code> in the context event will always be the
    same. Replication will therefore fail due to trying insert a row
    with a duplicate value for a primary key column.
  </p><p>
    In row based replication, the value that the engine returns for the
    row always be the same for each insert. This will result in the
    slave attempting to replay two insert log entries using the same
    value for the primary key column, and so replication will fail.
  </p><p>
    <span class="bold"><strong>Column Filtering</strong></span>
  </p><p>
    When using row-based replication,
    (<a class="link" href="replication.html#sysvar_binlog_format"><code class="literal">binlog_format=ROW</code></a>), a slave where
    the last columns are missing from a table is supported, as described
    in the section
    <a class="xref" href="replication.html#replication-features-differing-tables" title="17.4.1.9 Replication with Differing Table Definitions on Master and Slave">Section 17.4.1.9, “Replication with Differing Table Definitions on Master and Slave”</a>.
  </p><p>
    This filtering works on the slave side, that is, the columns are
    copied to the slave before they are filtered out. There are at least
    two cases where it is not desirable to copy the columns to the
    slave:
</p>
<div class="orderedlist">
<ol class="orderedlist" type="1"><li class="listitem"><p>
        If the data is confidential, so the slave server should not have
        access to it.
      </p></li><li class="listitem"><p>
        If the master has many slaves, filtering before sending to the
        slaves may reduce network traffic.
</p></li></ol>
</div>
<p>
    Master column filtering can be achieved using the
    <code class="literal">BLACKHOLE</code> engine. This is carried out in a way
    similar to how master table filtering is achieved - by using the
    <code class="literal">BLACKHOLE</code> engine and the
    <a class="link" href="replication.html#option_mysqld_replicate-do-table"><code class="option">--replicate-do-table</code></a> or
    <a class="link" href="replication.html#option_mysqld_replicate-ignore-table"><code class="option">--replicate-ignore-table</code></a> option.
  </p><p>
    The setup for the master is:
  </p><pre data-lang="sql" class="programlisting">
CREATE TABLE t1 (public_col_1, ..., public_col_N,
                 secret_col_1, ..., secret_col_M) ENGINE=MyISAM;
</pre><p>
    The setup for the trusted slave is:
  </p><pre data-lang="sql" class="programlisting">
CREATE TABLE t1 (public_col_1, ..., public_col_N) ENGINE=BLACKHOLE;
</pre><p>
    The setup for the untrusted slave is:
</p><pre data-lang="sql" class="programlisting">CREATE TABLE t1 (public_col_1, ..., public_col_N) ENGINE=MyISAM;</pre>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="merge-storage-engine"></a>16.7 The MERGE Storage Engine</h2>

</div>

</div>

</div>
<div class="toc">
<dl class="toc"><dt><span class="section"><a href="storage-engines.html#merge-table-advantages">16.7.1 MERGE Table Advantages and Disadvantages</a></span></dt><dt><span class="section"><a href="storage-engines.html#merge-table-problems">16.7.2 MERGE Table Problems</a></span></dt></dl>
</div>
<a class="indexterm" name="idm139899504211376"></a><a class="indexterm" name="idm139899504210336"></a><a class="indexterm" name="idm139899504208848"></a><a class="indexterm" name="idm139899504207360"></a><a class="indexterm" name="idm139899504205872"></a><p>
    The <code class="literal">MERGE</code> storage engine, also known as the
    <code class="literal">MRG_MyISAM</code> engine, is a collection of identical
    <code class="literal">MyISAM</code> tables that can be used as one.
    <span class="quote">“<span class="quote">Identical</span>”</span> means that all tables have identical column
    data types and index information. You cannot merge
    <code class="literal">MyISAM</code> tables in which the columns are listed in
    a different order, do not have exactly the same data types in
    corresponding columns, or have the indexes in different order.
    However, any or all of the <code class="literal">MyISAM</code> tables can be
    compressed with <a class="link" href="programs.html#myisampack" title="4.6.6 myisampack — Generate Compressed, Read-Only MyISAM Tables"><span class="command"><strong>myisampack</strong></span></a>. See
    <a class="xref" href="programs.html#myisampack" title="4.6.6 myisampack — Generate Compressed, Read-Only MyISAM Tables">Section 4.6.6, “<span class="command"><strong>myisampack</strong></span> — Generate Compressed, Read-Only MyISAM Tables”</a>. Differences between tables such as
    these do not matter:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
        Names of corresponding columns and indexes can differ.
      </p></li><li class="listitem"><p>
        Comments for tables, columns, and indexes can differ.
      </p></li><li class="listitem"><p>
        Table options such as <code class="literal">AVG_ROW_LENGTH</code>,
        <code class="literal">MAX_ROWS</code>, or <code class="literal">PACK_KEYS</code> can
        differ.
</p></li></ul>
</div>
<p>
    An alternative to a <code class="literal">MERGE</code> table is a partitioned
    table, which stores partitions of a single table in separate files
    and enables some operations to be performed more efficiently. For
    more information, see <a class="xref" href="partitioning.html" title="Chapter 22 Partitioning">Chapter 22, <i>Partitioning</i></a>.
  </p><p>
    When you create a <code class="literal">MERGE</code> table, MySQL creates a
    <code class="filename">.MRG</code> file on disk that contains the names of
    the underlying <code class="literal">MyISAM</code> tables that should be used
    as one. The table format of the <code class="literal">MERGE</code> table is
    stored in the MySQL data dictionary. The underlying tables do not
    have to be in the same database as the <code class="literal">MERGE</code>
    table.
  </p><p>
    You can use <a class="link" href="sql-syntax.html#select" title="13.2.10 SELECT Syntax"><code class="literal">SELECT</code></a>,
    <a class="link" href="sql-syntax.html#delete" title="13.2.2 DELETE Syntax"><code class="literal">DELETE</code></a>,
    <a class="link" href="sql-syntax.html#update" title="13.2.12 UPDATE Syntax"><code class="literal">UPDATE</code></a>, and
    <a class="link" href="sql-syntax.html#insert" title="13.2.6 INSERT Syntax"><code class="literal">INSERT</code></a> on <code class="literal">MERGE</code>
    tables. You must have <a class="link" href="security.html#priv_select"><code class="literal">SELECT</code></a>,
    <a class="link" href="security.html#priv_delete"><code class="literal">DELETE</code></a>, and
    <a class="link" href="security.html#priv_update"><code class="literal">UPDATE</code></a> privileges on the
    <code class="literal">MyISAM</code> tables that you map to a
    <code class="literal">MERGE</code> table.
</p>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Note
</div>
<p>
      The use of <code class="literal">MERGE</code> tables entails the following
      security issue: If a user has access to <code class="literal">MyISAM</code>
      table <em class="replaceable"><code>t</code></em>, that user can create a
      <code class="literal">MERGE</code> table <em class="replaceable"><code>m</code></em> that
      accesses <em class="replaceable"><code>t</code></em>. However, if the user's
      privileges on <em class="replaceable"><code>t</code></em> are subsequently
      revoked, the user can continue to access
      <em class="replaceable"><code>t</code></em> by doing so through
      <em class="replaceable"><code>m</code></em>.
</p>
</div>
<p>
    Use of <a class="link" href="sql-syntax.html#drop-table" title="13.1.29 DROP TABLE Syntax"><code class="literal">DROP TABLE</code></a> with a
    <code class="literal">MERGE</code> table drops only the
    <code class="literal">MERGE</code> specification. The underlying tables are
    not affected.
  </p><p>
    To create a <code class="literal">MERGE</code> table, you must specify a
    <code class="literal">UNION=(<em class="replaceable"><code>list-of-tables</code></em>)</code>
    option that indicates which <code class="literal">MyISAM</code> tables to use.
    You can optionally specify an <code class="literal">INSERT_METHOD</code>
    option to control how inserts into the <code class="literal">MERGE</code>
    table take place. Use a value of <code class="literal">FIRST</code> or
    <code class="literal">LAST</code> to cause inserts to be made in the first or
    last underlying table, respectively. If you specify no
    <code class="literal">INSERT_METHOD</code> option or if you specify it with a
    value of <code class="literal">NO</code>, inserts into the
    <code class="literal">MERGE</code> table are not permitted and attempts to do
    so result in an error.
  </p><p>
    The following example shows how to create a <code class="literal">MERGE</code>
    table:
  </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>CREATE TABLE t1 (</code></strong>
    -&gt;    <strong class="userinput"><code>a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,</code></strong>
    -&gt;    <strong class="userinput"><code>message CHAR(20)) ENGINE=MyISAM;</code></strong>
mysql&gt; <strong class="userinput"><code>CREATE TABLE t2 (</code></strong>
    -&gt;    <strong class="userinput"><code>a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,</code></strong>
    -&gt;    <strong class="userinput"><code>message CHAR(20)) ENGINE=MyISAM;</code></strong>
mysql&gt; <strong class="userinput"><code>INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');</code></strong>
mysql&gt; <strong class="userinput"><code>INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');</code></strong>
mysql&gt; <strong class="userinput"><code>CREATE TABLE total (</code></strong>
    -&gt;    <strong class="userinput"><code>a INT NOT NULL AUTO_INCREMENT,</code></strong>
    -&gt;    <strong class="userinput"><code>message CHAR(20), INDEX(a))</code></strong>
    -&gt;    <strong class="userinput"><code>ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;</code></strong>
</pre><p>
    Column <code class="literal">a</code> is indexed as a <code class="literal">PRIMARY
    KEY</code> in the underlying <code class="literal">MyISAM</code> tables,
    but not in the <code class="literal">MERGE</code> table. There it is indexed
    but not as a <code class="literal">PRIMARY KEY</code> because a
    <code class="literal">MERGE</code> table cannot enforce uniqueness over the
    set of underlying tables. (Similarly, a column with a
    <code class="literal">UNIQUE</code> index in the underlying tables should be
    indexed in the <code class="literal">MERGE</code> table but not as a
    <code class="literal">UNIQUE</code> index.)
  </p><p>
    After creating the <code class="literal">MERGE</code> table, you can use it to
    issue queries that operate on the group of tables as a whole:
  </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT * FROM total;</code></strong>
+---+---------+
| a | message |
+---+---------+
| 1 | Testing |
| 2 | table   |
| 3 | t1      |
| 1 | Testing |
| 2 | table   |
| 3 | t2      |
+---+---------+
</pre><p>
    To remap a <code class="literal">MERGE</code> table to a different collection
    of <code class="literal">MyISAM</code> tables, you can use one of the
    following methods:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
        <code class="literal">DROP</code> the <code class="literal">MERGE</code> table and
        re-create it.
      </p></li><li class="listitem"><p>
        Use <code class="literal">ALTER TABLE <em class="replaceable"><code>tbl_name</code></em>
        UNION=(...)</code> to change the list of underlying tables.
      </p><p>
        It is also possible to use <code class="literal">ALTER TABLE ...
        UNION=()</code> (that is, with an empty
        <a class="link" href="sql-syntax.html#union" title="13.2.10.3 UNION Syntax"><code class="literal">UNION</code></a> clause) to remove all of
        the underlying tables. However, in this case, the table is
        effectively empty and inserts fail because there is no
        underlying table to take new rows. Such a table might be useful
        as a template for creating new <code class="literal">MERGE</code> tables
        with <a class="link" href="sql-syntax.html#create-table-like" title="13.1.18.4 CREATE TABLE ... LIKE Syntax"><code class="literal">CREATE
        TABLE ... LIKE</code></a>.
</p></li></ul>
</div>
<p>
    The underlying table definitions and indexes must conform closely to
    the definition of the <code class="literal">MERGE</code> table. Conformance is
    checked when a table that is part of a <code class="literal">MERGE</code>
    table is opened, not when the <code class="literal">MERGE</code> table is
    created. If any table fails the conformance checks, the operation
    that triggered the opening of the table fails. This means that
    changes to the definitions of tables within a
    <code class="literal">MERGE</code> may cause a failure when the
    <code class="literal">MERGE</code> table is accessed. The conformance checks
    applied to each table are:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
        The underlying table and the <code class="literal">MERGE</code> table must
        have the same number of columns.
      </p></li><li class="listitem"><p>
        The column order in the underlying table and the
        <code class="literal">MERGE</code> table must match.
      </p></li><li class="listitem"><p>
        Additionally, the specification for each corresponding column in
        the parent <code class="literal">MERGE</code> table and the underlying
        tables are compared and must satisfy these checks:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>
            The column type in the underlying table and the
            <code class="literal">MERGE</code> table must be equal.
          </p></li><li class="listitem"><p>
            The column length in the underlying table and the
            <code class="literal">MERGE</code> table must be equal.
          </p></li><li class="listitem"><p>
            The column of the underlying table and the
            <code class="literal">MERGE</code> table can be
            <code class="literal">NULL</code>.
</p></li></ul>
</div>
</li><li class="listitem"><p>
        The underlying table must have at least as many indexes as the
        <code class="literal">MERGE</code> table. The underlying table may have
        more indexes than the <code class="literal">MERGE</code> table, but cannot
        have fewer.
</p>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Note
</div>
<p>
          A known issue exists where indexes on the same columns must be
          in identical order, in both the <code class="literal">MERGE</code> table
          and the underlying <code class="literal">MyISAM</code> table. See Bug
          #33653.
</p>
</div>
<p>
        Each index must satisfy these checks:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>
            The index type of the underlying table and the
            <code class="literal">MERGE</code> table must be the same.
          </p></li><li class="listitem"><p>
            The number of index parts (that is, multiple columns within
            a compound index) in the index definition for the underlying
            table and the <code class="literal">MERGE</code> table must be the
            same.
          </p></li><li class="listitem"><p>
            For each index part:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: square; "><li class="listitem"><p>
                Index part lengths must be equal.
              </p></li><li class="listitem"><p>
                Index part types must be equal.
              </p></li><li class="listitem"><p>
                Index part languages must be equal.
              </p></li><li class="listitem"><p>
                Check whether index parts can be
                <code class="literal">NULL</code>.
</p></li></ul>
</div>
</li></ul>
</div>
</li></ul>
</div>
<p>
    If a <code class="literal">MERGE</code> table cannot be opened or used because
    of a problem with an underlying table, <a class="link" href="sql-syntax.html#check-table" title="13.7.3.2 CHECK TABLE Syntax"><code class="literal">CHECK
    TABLE</code></a> displays information about which table caused the
    problem.
</p>
<h3><a name="idm139899504090272"></a>Additional Resources</h3>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
        A forum dedicated to the <code class="literal">MERGE</code> storage engine
        is available at <a class="ulink" href="http://forums.mysql.com/list.php?93" target="_top">http://forums.mysql.com/list.php?93</a>.
</p></li></ul>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="merge-table-advantages"></a>16.7.1 MERGE Table Advantages and Disadvantages</h3>

</div>

</div>

</div>
<p>
      <code class="literal">MERGE</code> tables can help you solve the following
      problems:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          Easily manage a set of log tables. For example, you can put
          data from different months into separate tables, compress some
          of them with <a class="link" href="programs.html#myisampack" title="4.6.6 myisampack — Generate Compressed, Read-Only MyISAM Tables"><span class="command"><strong>myisampack</strong></span></a>, and then create a
          <code class="literal">MERGE</code> table to use them as one.
        </p></li><li class="listitem"><p>
          Obtain more speed. You can split a large read-only table based
          on some criteria, and then put individual tables on different
          disks. A <code class="literal">MERGE</code> table structured this way
          could be much faster than using a single large table.
        </p></li><li class="listitem"><p>
          Perform more efficient searches. If you know exactly what you
          are looking for, you can search in just one of the underlying
          tables for some queries and use a <code class="literal">MERGE</code>
          table for others. You can even have many different
          <code class="literal">MERGE</code> tables that use overlapping sets of
          tables.
        </p></li><li class="listitem"><p>
          Perform more efficient repairs. It is easier to repair
          individual smaller tables that are mapped to a
          <code class="literal">MERGE</code> table than to repair a single large
          table.
        </p></li><li class="listitem"><p>
          Instantly map many tables as one. A <code class="literal">MERGE</code>
          table need not maintain an index of its own because it uses
          the indexes of the individual tables. As a result,
          <code class="literal">MERGE</code> table collections are
          <span class="emphasis"><em>very</em></span> fast to create or remap. (You must
          still specify the index definitions when you create a
          <code class="literal">MERGE</code> table, even though no indexes are
          created.)
        </p></li><li class="listitem"><p>
          If you have a set of tables from which you create a large
          table on demand, you can instead create a
          <code class="literal">MERGE</code> table from them on demand. This is
          much faster and saves a lot of disk space.
        </p></li><li class="listitem"><p>
          Exceed the file size limit for the operating system. Each
          <code class="literal">MyISAM</code> table is bound by this limit, but a
          collection of <code class="literal">MyISAM</code> tables is not.
        </p></li><li class="listitem"><p>
          You can create an alias or synonym for a
          <code class="literal">MyISAM</code> table by defining a
          <code class="literal">MERGE</code> table that maps to that single table.
          There should be no really notable performance impact from
          doing this (only a couple of indirect calls and
          <code class="literal">memcpy()</code> calls for each read).
</p></li></ul>
</div>
<p>
      The disadvantages of <code class="literal">MERGE</code> tables are:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          You can use only identical <code class="literal">MyISAM</code> tables
          for a <code class="literal">MERGE</code> table.
        </p></li><li class="listitem"><p>
          Some <code class="literal">MyISAM</code> features are unavailable in
          <code class="literal">MERGE</code> tables. For example, you cannot
          create <code class="literal">FULLTEXT</code> indexes on
          <code class="literal">MERGE</code> tables. (You can create
          <code class="literal">FULLTEXT</code> indexes on the underlying
          <code class="literal">MyISAM</code> tables, but you cannot search the
          <code class="literal">MERGE</code> table with a full-text search.)
        </p></li><li class="listitem"><p>
          If the <code class="literal">MERGE</code> table is nontemporary, all
          underlying <code class="literal">MyISAM</code> tables must be
          nontemporary. If the <code class="literal">MERGE</code> table is
          temporary, the <code class="literal">MyISAM</code> tables can be any mix
          of temporary and nontemporary.
        </p></li><li class="listitem"><p>
          <code class="literal">MERGE</code> tables use more file descriptors than
          <code class="literal">MyISAM</code> tables. If 10 clients are using a
          <code class="literal">MERGE</code> table that maps to 10 tables, the
          server uses (10 × 10) + 10 file descriptors. (10 data
          file descriptors for each of the 10 clients, and 10 index file
          descriptors shared among the clients.)
        </p></li><li class="listitem"><p>
          Index reads are slower. When you read an index, the
          <code class="literal">MERGE</code> storage engine needs to issue a read
          on all underlying tables to check which one most closely
          matches a given index value. To read the next index value, the
          <code class="literal">MERGE</code> storage engine needs to search the
          read buffers to find the next value. Only when one index
          buffer is used up does the storage engine need to read the
          next index block. This makes <code class="literal">MERGE</code> indexes
          much slower on <a class="link" href="optimization.html#jointype_eq_ref"><code class="literal">eq_ref</code></a>
          searches, but not much slower on
          <a class="link" href="optimization.html#jointype_ref"><code class="literal">ref</code></a> searches. For more
          information about <a class="link" href="optimization.html#jointype_eq_ref"><code class="literal">eq_ref</code></a>
          and <a class="link" href="optimization.html#jointype_ref"><code class="literal">ref</code></a>, see
          <a class="xref" href="sql-syntax.html#explain" title="13.8.2 EXPLAIN Syntax">Section 13.8.2, “EXPLAIN Syntax”</a>.
</p></li></ul>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="merge-table-problems"></a>16.7.2 MERGE Table Problems</h3>

</div>

</div>

</div>
<p>
      The following are known problems with <code class="literal">MERGE</code>
      tables:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          In versions of MySQL Server prior to 5.1.23, it was possible
          to create temporary merge tables with nontemporary child
          MyISAM tables.
        </p><p>
          From versions 5.1.23, MERGE children were locked through the
          parent table. If the parent was temporary, it was not locked
          and so the children were not locked either. Parallel use of
          the MyISAM tables corrupted them.
        </p></li><li class="listitem"><p>
          If you use <a class="link" href="sql-syntax.html#alter-table" title="13.1.8 ALTER TABLE Syntax"><code class="literal">ALTER TABLE</code></a> to
          change a <code class="literal">MERGE</code> table to another storage
          engine, the mapping to the underlying tables is lost. Instead,
          the rows from the underlying <code class="literal">MyISAM</code> tables
          are copied into the altered table, which then uses the
          specified storage engine.
        </p></li><li class="listitem"><p>
          The <code class="literal">INSERT_METHOD</code> table option for a
          <code class="literal">MERGE</code> table indicates which underlying
          <code class="literal">MyISAM</code> table to use for inserts into the
          <code class="literal">MERGE</code> table. However, use of the
          <code class="literal">AUTO_INCREMENT</code> table option for that
          <code class="literal">MyISAM</code> table has no effect for inserts into
          the <code class="literal">MERGE</code> table until at least one row has
          been inserted directly into the <code class="literal">MyISAM</code>
          table.
        </p></li><li class="listitem"><p>
          A <code class="literal">MERGE</code> table cannot maintain uniqueness
          constraints over the entire table. When you perform an
          <a class="link" href="sql-syntax.html#insert" title="13.2.6 INSERT Syntax"><code class="literal">INSERT</code></a>, the data goes into the
          first or last <code class="literal">MyISAM</code> table (as determined
          by the <code class="literal">INSERT_METHOD</code> option). MySQL ensures
          that unique key values remain unique within that
          <code class="literal">MyISAM</code> table, but not over all the
          underlying tables in the collection.
        </p></li><li class="listitem"><p>
          Because the <code class="literal">MERGE</code> engine cannot enforce
          uniqueness over the set of underlying tables,
          <a class="link" href="sql-syntax.html#replace" title="13.2.9 REPLACE Syntax"><code class="literal">REPLACE</code></a> does not work as
          expected. The two key facts are:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>
              <a class="link" href="sql-syntax.html#replace" title="13.2.9 REPLACE Syntax"><code class="literal">REPLACE</code></a> can detect unique
              key violations only in the underlying table to which it is
              going to write (which is determined by the
              <code class="literal">INSERT_METHOD</code> option). This differs
              from violations in the <code class="literal">MERGE</code> table
              itself.
            </p></li><li class="listitem"><p>
              If <a class="link" href="sql-syntax.html#replace" title="13.2.9 REPLACE Syntax"><code class="literal">REPLACE</code></a> detects a unique
              key violation, it will change only the corresponding row
              in the underlying table it is writing to; that is, the
              first or last table, as determined by the
              <code class="literal">INSERT_METHOD</code> option.
</p></li></ul>
</div>
<p>
          Similar considerations apply for
          <a class="link" href="sql-syntax.html#insert-on-duplicate" title="13.2.6.2 INSERT ... ON DUPLICATE KEY UPDATE Syntax"><code class="literal">INSERT
          ... ON DUPLICATE KEY UPDATE</code></a>.
        </p></li><li class="listitem"><p>
          <code class="literal">MERGE</code> tables do not support partitioning.
          That is, you cannot partition a <code class="literal">MERGE</code>
          table, nor can any of a <code class="literal">MERGE</code> table's
          underlying <code class="literal">MyISAM</code> tables be partitioned.
        </p></li><li class="listitem"><p>
          You should not use <a class="link" href="sql-syntax.html#analyze-table" title="13.7.3.1 ANALYZE TABLE Syntax"><code class="literal">ANALYZE
          TABLE</code></a>, <a class="link" href="sql-syntax.html#repair-table" title="13.7.3.5 REPAIR TABLE Syntax"><code class="literal">REPAIR TABLE</code></a>,
          <a class="link" href="sql-syntax.html#optimize-table" title="13.7.3.4 OPTIMIZE TABLE Syntax"><code class="literal">OPTIMIZE TABLE</code></a>,
          <a class="link" href="sql-syntax.html#alter-table" title="13.1.8 ALTER TABLE Syntax"><code class="literal">ALTER TABLE</code></a>,
          <a class="link" href="sql-syntax.html#drop-table" title="13.1.29 DROP TABLE Syntax"><code class="literal">DROP TABLE</code></a>,
          <a class="link" href="sql-syntax.html#delete" title="13.2.2 DELETE Syntax"><code class="literal">DELETE</code></a> without a
          <code class="literal">WHERE</code> clause, or
          <a class="link" href="sql-syntax.html#truncate-table" title="13.1.34 TRUNCATE TABLE Syntax"><code class="literal">TRUNCATE TABLE</code></a> on any of the
          tables that are mapped into an open <code class="literal">MERGE</code>
          table. If you do so, the <code class="literal">MERGE</code> table may
          still refer to the original table and yield unexpected
          results. To work around this problem, ensure that no
          <code class="literal">MERGE</code> tables remain open by issuing a
          <a class="link" href="sql-syntax.html#flush-tables"><code class="literal">FLUSH TABLES</code></a> statement prior to
          performing any of the named operations.
        </p><p>
          The unexpected results include the possibility that the
          operation on the <code class="literal">MERGE</code> table will report
          table corruption. If this occurs after one of the named
          operations on the underlying <code class="literal">MyISAM</code> tables,
          the corruption message is spurious. To deal with this, issue a
          <a class="link" href="sql-syntax.html#flush-tables"><code class="literal">FLUSH TABLES</code></a> statement after
          modifying the <code class="literal">MyISAM</code> tables.
        </p></li><li class="listitem"><p>
          <a class="link" href="sql-syntax.html#drop-table" title="13.1.29 DROP TABLE Syntax"><code class="literal">DROP TABLE</code></a> on a table that is
          in use by a <code class="literal">MERGE</code> table does not work on
          Windows because the <code class="literal">MERGE</code> storage engine's
          table mapping is hidden from the upper layer of MySQL. Windows
          does not permit open files to be deleted, so you first must
          flush all <code class="literal">MERGE</code> tables (with
          <a class="link" href="sql-syntax.html#flush-tables"><code class="literal">FLUSH TABLES</code></a>) or drop the
          <code class="literal">MERGE</code> table before dropping the table.
        </p></li><li class="listitem"><p>
          The definition of the <code class="literal">MyISAM</code> tables and the
          <code class="literal">MERGE</code> table are checked when the tables are
          accessed (for example, as part of a
          <a class="link" href="sql-syntax.html#select" title="13.2.10 SELECT Syntax"><code class="literal">SELECT</code></a> or
          <a class="link" href="sql-syntax.html#insert" title="13.2.6 INSERT Syntax"><code class="literal">INSERT</code></a> statement). The checks
          ensure that the definitions of the tables and the parent
          <code class="literal">MERGE</code> table definition match by comparing
          column order, types, sizes and associated indexes. If there is
          a difference between the tables, an error is returned and the
          statement fails. Because these checks take place when the
          tables are opened, any changes to the definition of a single
          table, including column changes, column ordering, and engine
          alterations will cause the statement to fail.
        </p></li><li class="listitem"><p>
          The order of indexes in the <code class="literal">MERGE</code> table and
          its underlying tables should be the same. If you use
          <a class="link" href="sql-syntax.html#alter-table" title="13.1.8 ALTER TABLE Syntax"><code class="literal">ALTER TABLE</code></a> to add a
          <code class="literal">UNIQUE</code> index to a table used in a
          <code class="literal">MERGE</code> table, and then use
          <a class="link" href="sql-syntax.html#alter-table" title="13.1.8 ALTER TABLE Syntax"><code class="literal">ALTER TABLE</code></a> to add a nonunique
          index on the <code class="literal">MERGE</code> table, the index
          ordering is different for the tables if there was already a
          nonunique index in the underlying table. (This happens because
          <a class="link" href="sql-syntax.html#alter-table" title="13.1.8 ALTER TABLE Syntax"><code class="literal">ALTER TABLE</code></a> puts
          <code class="literal">UNIQUE</code> indexes before nonunique indexes to
          facilitate rapid detection of duplicate keys.) Consequently,
          queries on tables with such indexes may return unexpected
          results.
        </p></li><li class="listitem"><p>
          If you encounter an error message similar to <span class="errortext">ERROR
          1017 (HY000): Can't find file:
          '<em class="replaceable"><code>tbl_name</code></em>.MRG' (errno:
          2)</span>, it generally indicates that some of the
          underlying tables do not use the <code class="literal">MyISAM</code>
          storage engine. Confirm that all of these tables are
          <code class="literal">MyISAM</code>.
        </p></li><li class="listitem"><p>
          The maximum number of rows in a <code class="literal">MERGE</code> table
          is 2<sup>64</sup> (~1.844E+19; the same as for
          a <code class="literal">MyISAM</code> table). It is not possible to
          merge multiple <code class="literal">MyISAM</code> tables into a single
          <code class="literal">MERGE</code> table that would have more than this
          number of rows.
        </p></li><li class="listitem"><p>
          Use of underlying <code class="literal">MyISAM</code> tables of
          differing row formats with a parent <code class="literal">MERGE</code>
          table is currently known to fail. See Bug #32364.
        </p></li><li class="listitem"><p>
          You cannot change the union list of a nontemporary
          <code class="literal">MERGE</code> table when <a class="link" href="sql-syntax.html#lock-tables" title="13.3.6 LOCK TABLES and UNLOCK TABLES Syntax"><code class="literal">LOCK
          TABLES</code></a> is in effect. The following does
          <span class="emphasis"><em>not</em></span> work:
        </p><pre data-lang="sql" class="programlisting">
CREATE TABLE m1 ... ENGINE=MRG_MYISAM ...;
LOCK TABLES t1 WRITE, t2 WRITE, m1 WRITE;
ALTER TABLE m1 ... UNION=(t1,t2) ...;
</pre><p>
          However, you can do this with a temporary
          <code class="literal">MERGE</code> table.
        </p></li><li class="listitem"><p>
          You cannot create a <code class="literal">MERGE</code> table with
          <code class="literal">CREATE ... SELECT</code>, neither as a temporary
          <code class="literal">MERGE</code> table, nor as a nontemporary
          <code class="literal">MERGE</code> table. For example:
        </p><pre data-lang="sql" class="programlisting">CREATE TABLE m1 ... ENGINE=MRG_MYISAM ... SELECT ...;</pre><p>
          Attempts to do this result in an error:
          <em class="replaceable"><code>tbl_name</code></em> is not <code class="literal">BASE
          TABLE</code>.
        </p></li><li class="listitem"><p>
          In some cases, differing <code class="literal">PACK_KEYS</code> table
          option values among the <code class="literal">MERGE</code> and
          underlying tables cause unexpected results if the underlying
          tables contain <code class="literal">CHAR</code> or
          <code class="literal">BINARY</code> columns. As a workaround, use
          <code class="literal">ALTER TABLE</code> to ensure that all involved
          tables have the same <code class="literal">PACK_KEYS</code> value. (Bug
          #50646)
</p></li></ul>
</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="federated-storage-engine"></a>16.8 The FEDERATED Storage Engine</h2>

</div>

</div>

</div>
<div class="toc">
<dl class="toc"><dt><span class="section"><a href="storage-engines.html#federated-description">16.8.1 FEDERATED Storage Engine Overview</a></span></dt><dt><span class="section"><a href="storage-engines.html#federated-create">16.8.2 How to Create FEDERATED Tables</a></span></dt><dt><span class="section"><a href="storage-engines.html#federated-usagenotes">16.8.3 FEDERATED Storage Engine Notes and Tips</a></span></dt><dt><span class="section"><a href="storage-engines.html#federated-storage-engine-resources">16.8.4 FEDERATED Storage Engine Resources</a></span></dt></dl>
</div>
<a class="indexterm" name="idm139899503935360"></a><a class="indexterm" name="idm139899503934272"></a><p>
    The <code class="literal">FEDERATED</code> storage engine lets you access data
    from a remote MySQL database without using replication or cluster
    technology. Querying a local <code class="literal">FEDERATED</code> table
    automatically pulls the data from the remote (federated) tables. No
    data is stored on the local tables.
  </p><p>
    To include the <code class="literal">FEDERATED</code> storage engine if you
    build MySQL from source, invoke <span class="command"><strong>CMake</strong></span> with the
    <a class="link" href="installing.html#option_cmake_storage_engine_options" title="Storage Engine Options"><code class="option">-DWITH_FEDERATED_STORAGE_ENGINE</code></a>
    option.
  </p><p>
    The <code class="literal">FEDERATED</code> storage engine is not enabled by
    default in the running server; to enable
    <code class="literal">FEDERATED</code>, you must start the MySQL server binary
    using the <code class="option">--federated</code> option.
  </p><p>
    To examine the source for the <code class="literal">FEDERATED</code> engine,
    look in the <code class="filename">storage/federated</code> directory of a
    MySQL source distribution.
</p>
<div class="section">

<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="federated-description"></a>16.8.1 FEDERATED Storage Engine Overview</h3>
</div>
</div>
</div>
<p>
      When you create a table using one of the standard storage engines
      (such as <code class="literal">MyISAM</code>, <code class="literal">CSV</code> or
      <code class="literal">InnoDB</code>), the table consists of the table
      definition and the associated data. When you create a
      <code class="literal">FEDERATED</code> table, the table definition is the
      same, but the physical storage of the data is handled on a remote
      server.
    </p><p>
      A <code class="literal">FEDERATED</code> table consists of two elements:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          A <span class="emphasis"><em>remote server</em></span> with a database table,
          which in turn consists of the table definition (stored in the
          MySQL data dictionary) and the associated table. The table
          type of the remote table may be any type supported by the
          remote <code class="literal">mysqld</code> server, including
          <code class="literal">MyISAM</code> or <code class="literal">InnoDB</code>.
        </p></li><li class="listitem"><p>
          A <span class="emphasis"><em>local server</em></span> with a database table,
          where the table definition matches that of the corresponding
          table on the remote server. The table definition is stored in
          the data dictionary. There is no data file on the local
          server. Instead, the table definition includes a connection
          string that points to the remote table.
</p></li></ul>
</div>
<p>
      When executing queries and statements on a
      <code class="literal">FEDERATED</code> table on the local server, the
      operations that would normally insert, update or delete
      information from a local data file are instead sent to the remote
      server for execution, where they update the data file on the
      remote server or return matching rows from the remote server.
    </p><p>
      The basic structure of a <code class="literal">FEDERATED</code> table setup
      is shown in <a class="xref" href="storage-engines.html#figure-se-federated-structure" title="Figure 16.2 FEDERATED Table Structure">Figure 16.2, “FEDERATED Table Structure”</a>.
</p>
<div class="figure">
<a name="figure-se-federated-structure"></a><p class="title"><b>Figure 16.2 FEDERATED Table Structure</b></p>
<div class="figure-contents">

<div class="mediaobject">
<img src="images/se-federated-structure.png" width="475" height="269" alt="Content is described in the surrounding text.">
</div>

</div>

</div>
<br class="figure-break"><p>
      When a client issues an SQL statement that refers to a
      <code class="literal">FEDERATED</code> table, the flow of information
      between the local server (where the SQL statement is executed) and
      the remote server (where the data is physically stored) is as
      follows:
</p>
<div class="orderedlist">
<ol class="orderedlist" type="1"><li class="listitem"><p>
          The storage engine looks through each column that the
          <code class="literal">FEDERATED</code> table has and constructs an
          appropriate SQL statement that refers to the remote table.
        </p></li><li class="listitem"><p>
          The statement is sent to the remote server using the MySQL
          client API.
        </p></li><li class="listitem"><p>
          The remote server processes the statement and the local server
          retrieves any result that the statement produces (an
          affected-rows count or a result set).
        </p></li><li class="listitem"><p>
          If the statement produces a result set, each column is
          converted to internal storage engine format that the
          <code class="literal">FEDERATED</code> engine expects and can use to
          display the result to the client that issued the original
          statement.
</p></li></ol>
</div>
<p>
      The local server communicates with the remote server using MySQL
      client C API functions. It invokes
      <a class="link" href="connectors-apis.html#mysql-real-query" title="27.7.7.57 mysql_real_query()"><code class="literal">mysql_real_query()</code></a> to send the
      statement. To read a result set, it uses
      <a class="link" href="connectors-apis.html#mysql-store-result" title="27.7.7.79 mysql_store_result()"><code class="literal">mysql_store_result()</code></a> and fetches
      rows one at a time using
      <a class="link" href="connectors-apis.html#mysql-fetch-row" title="27.7.7.21 mysql_fetch_row()"><code class="literal">mysql_fetch_row()</code></a>.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="federated-create"></a>16.8.2 How to Create FEDERATED Tables</h3>

</div>

</div>

</div>
<div class="toc">
<dl class="toc"><dt><span class="section"><a href="storage-engines.html#federated-create-connection">16.8.2.1 Creating a FEDERATED Table Using CONNECTION</a></span></dt><dt><span class="section"><a href="storage-engines.html#federated-create-server">16.8.2.2 Creating a FEDERATED Table Using CREATE SERVER</a></span></dt></dl>
</div>
<p>
      To create a <code class="literal">FEDERATED</code> table you should follow
      these steps:
</p>
<div class="orderedlist">
<ol class="orderedlist" type="1"><li class="listitem"><p>
          Create the table on the remote server. Alternatively, make a
          note of the table definition of an existing table, perhaps
          using the <a class="link" href="sql-syntax.html#show-create-table" title="13.7.6.10 SHOW CREATE TABLE Syntax"><code class="literal">SHOW CREATE TABLE</code></a>
          statement.
        </p></li><li class="listitem"><p>
          Create the table on the local server with an identical table
          definition, but adding the connection information that links
          the local table to the remote table.
</p></li></ol>
</div>
<p>
      For example, you could create the following table on the remote
      server:
    </p><pre data-lang="sql" class="programlisting">
CREATE TABLE test_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=MyISAM
DEFAULT CHARSET=utf8mb4;
</pre><p>
      To create the local table that will be federated to the remote
      table, there are two options available. You can either create the
      local table and specify the connection string (containing the
      server name, login, password) to be used to connect to the remote
      table using the <code class="literal">CONNECTION</code>, or you can use an
      existing connection that you have previously created using the
      <a class="link" href="sql-syntax.html#create-server" title="13.1.16 CREATE SERVER Syntax"><code class="literal">CREATE SERVER</code></a> statement.
</p>
<div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Important
</div>
<p>
        When you create the local table it <span class="emphasis"><em>must</em></span>
        have an identical field definition to the remote table.
</p>
</div>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
<div class="admon-title">
Note
</div>
<p>
        You can improve the performance of a
        <code class="literal">FEDERATED</code> table by adding indexes to the
        table on the host. The optimization will occur because the query
        sent to the remote server will include the contents of the
        <code class="literal">WHERE</code> clause and will be sent to the remote
        server and subsequently executed locally. This reduces the
        network traffic that would otherwise request the entire table
        from the server for local processing.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="federated-create-connection"></a>16.8.2.1 Creating a FEDERATED Table Using CONNECTION</h4>

</div>

</div>

</div>
<p>
        To use the first method, you must specify the
        <code class="literal">CONNECTION</code> string after the engine type in a
        <a class="link" href="sql-syntax.html#create-table" title="13.1.18 CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a> statement. For
        example:
      </p><pre data-lang="sql" class="programlisting">
CREATE TABLE federated_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=utf8mb4
CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table';
</pre>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Note
</div>
<p>
          <code class="literal">CONNECTION</code> replaces the
          <code class="literal">COMMENT</code> used in some previous versions of
          MySQL.
</p>
</div>
<p>
        The <code class="literal">CONNECTION</code> string contains the
        information required to connect to the remote server containing
        the table that will be used to physically store the data. The
        connection string specifies the server name, login credentials,
        port number and database/table information. In the example, the
        remote table is on the server <code class="literal">remote_host</code>,
        using port 9306. The name and port number should match the host
        name (or IP address) and port number of the remote MySQL server
        instance you want to use as your remote table.
      </p><p>
        The format of the connection string is as follows:
      </p><pre data-lang="none" class="programlisting">
<em class="replaceable"><code>scheme</code></em>://<em class="replaceable"><code>user_name</code></em>[:<em class="replaceable"><code>password</code></em>]@<em class="replaceable"><code>host_name</code></em>[:<em class="replaceable"><code>port_num</code></em>]/<em class="replaceable"><code>db_name</code></em>/<em class="replaceable"><code>tbl_name</code></em>
</pre><p>
        Where:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            <em class="replaceable"><code>scheme</code></em>: A recognized connection
            protocol. Only <code class="literal">mysql</code> is supported as the
            <em class="replaceable"><code>scheme</code></em> value at this point.
          </p></li><li class="listitem"><p>
            <em class="replaceable"><code>user_name</code></em>: The user name for the
            connection. This user must have been created on the remote
            server, and must have suitable privileges to perform the
            required actions (<a class="link" href="sql-syntax.html#select" title="13.2.10 SELECT Syntax"><code class="literal">SELECT</code></a>,
            <a class="link" href="sql-syntax.html#insert" title="13.2.6 INSERT Syntax"><code class="literal">INSERT</code></a>,
            <a class="link" href="sql-syntax.html#update" title="13.2.12 UPDATE Syntax"><code class="literal">UPDATE</code></a>, and so forth) on the
            remote table.
          </p></li><li class="listitem"><p>
            <em class="replaceable"><code>password</code></em>: (Optional) The
            corresponding password for
            <em class="replaceable"><code>user_name</code></em>.
          </p></li><li class="listitem"><p>
            <em class="replaceable"><code>host_name</code></em>: The host name or IP
            address of the remote server.
          </p></li><li class="listitem"><p>
            <em class="replaceable"><code>port_num</code></em>: (Optional) The port
            number for the remote server. The default is 3306.
          </p></li><li class="listitem"><p>
            <em class="replaceable"><code>db_name</code></em>: The name of the database
            holding the remote table.
          </p></li><li class="listitem"><p>
            <em class="replaceable"><code>tbl_name</code></em>: The name of the remote
            table. The name of the local and the remote table do not
            have to match.
</p></li></ul>
</div>
<p>
        Sample connection strings:
      </p><pre data-lang="sql" class="programlisting">
CONNECTION='mysql://username:password@hostname:port/database/tablename'
CONNECTION='mysql://username@hostname/database/tablename'
CONNECTION='mysql://username:password@hostname/database/tablename'
</pre>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="federated-create-server"></a>16.8.2.2 Creating a FEDERATED Table Using CREATE SERVER</h4>

</div>

</div>

</div>
<p>
        If you are creating a number of <code class="literal">FEDERATED</code>
        tables on the same server, or if you want to simplify the
        process of creating <code class="literal">FEDERATED</code> tables, you can
        use the <a class="link" href="sql-syntax.html#create-server" title="13.1.16 CREATE SERVER Syntax"><code class="literal">CREATE SERVER</code></a> statement
        to define the server connection parameters, just as you would
        with the <code class="literal">CONNECTION</code> string.
      </p><p>
        The format of the <a class="link" href="sql-syntax.html#create-server" title="13.1.16 CREATE SERVER Syntax"><code class="literal">CREATE SERVER</code></a>
        statement is:
      </p><pre data-lang="sql" class="programlisting">CREATE SERVER
<em class="replaceable"><code>server_name</code></em>
FOREIGN DATA WRAPPER <em class="replaceable"><code>wrapper_name</code></em>
OPTIONS (<em class="replaceable"><code>option</code></em> [, <em class="replaceable"><code>option</code></em>] ...)</pre><p>
        The <em class="replaceable"><code>server_name</code></em> is used in the
        connection string when creating a new
        <code class="literal">FEDERATED</code> table.
      </p><p>
        For example, to create a server connection identical to the
        <code class="literal">CONNECTION</code> string:
      </p><pre data-lang="sql" class="programlisting">CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table';</pre><p>
        You would use the following statement:
      </p><pre data-lang="sql" class="programlisting">CREATE SERVER fedlink
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'fed_user', HOST 'remote_host', PORT 9306, DATABASE 'federated');</pre><p>
        To create a <code class="literal">FEDERATED</code> table that uses this
        connection, you still use the <code class="literal">CONNECTION</code>
        keyword, but specify the name you used in the
        <a class="link" href="sql-syntax.html#create-server" title="13.1.16 CREATE SERVER Syntax"><code class="literal">CREATE SERVER</code></a> statement.
      </p><pre data-lang="sql" class="programlisting">CREATE TABLE test_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=utf8mb4
CONNECTION='fedlink/test_table';</pre><p>
        The connection name in this example contains the name of the
        connection (<code class="literal">fedlink</code>) and the name of the
        table (<code class="literal">test_table</code>) to link to, separated by a
        slash. If you specify only the connection name without a table
        name, the table name of the local table is used instead.
      </p><p>
        For more information on <a class="link" href="sql-syntax.html#create-server" title="13.1.16 CREATE SERVER Syntax"><code class="literal">CREATE
        SERVER</code></a>, see <a class="xref" href="sql-syntax.html#create-server" title="13.1.16 CREATE SERVER Syntax">Section 13.1.16, “CREATE SERVER Syntax”</a>.
      </p><p>
        The <a class="link" href="sql-syntax.html#create-server" title="13.1.16 CREATE SERVER Syntax"><code class="literal">CREATE SERVER</code></a> statement
        accepts the same arguments as the <code class="literal">CONNECTION</code>
        string. The <a class="link" href="sql-syntax.html#create-server" title="13.1.16 CREATE SERVER Syntax"><code class="literal">CREATE SERVER</code></a>
        statement updates the rows in the
        <code class="literal">mysql.servers</code> table. See the following table
        for information on the correspondence between parameters in a
        connection string, options in the <a class="link" href="sql-syntax.html#create-server" title="13.1.16 CREATE SERVER Syntax"><code class="literal">CREATE
        SERVER</code></a> statement, and the columns in the
        <code class="literal">mysql.servers</code> table. For reference, the
        format of the <code class="literal">CONNECTION</code> string is as
        follows:
      </p><pre data-lang="none" class="programlisting">
<em class="replaceable"><code>scheme</code></em>://<em class="replaceable"><code>user_name</code></em>[:<em class="replaceable"><code>password</code></em>]@<em class="replaceable"><code>host_name</code></em>[:<em class="replaceable"><code>port_num</code></em>]/<em class="replaceable"><code>db_name</code></em>/<em class="replaceable"><code>tbl_name</code></em>
</pre>
<div class="informaltable">
<table summary="The correspondence between parameters in a connection string, options in the CREATE SERVER statement, and the columns in the mysql.servers table."><col width="25%"><col width="25%"><col width="25%"><col width="25%"><thead><tr>
            <th scope="col">Description</th>
            <th scope="col"><code class="literal">CONNECTION</code> string</th>
            <th scope="col"><a class="link" href="sql-syntax.html#create-server" title="13.1.16 CREATE SERVER Syntax"><code class="literal">CREATE SERVER</code></a> option</th>
            <th scope="col"><code class="literal">mysql.servers</code> column</th>
          </tr></thead><tbody><tr>
            <td scope="row">Connection scheme</td>
            <td><em class="replaceable"><code>scheme</code></em></td>
            <td><code class="literal">wrapper_name</code></td>
            <td><code class="literal">Wrapper</code></td>
          </tr><tr>
            <td scope="row">Remote user</td>
            <td><em class="replaceable"><code>user_name</code></em></td>
            <td><code class="literal">USER</code></td>
            <td><code class="literal">Username</code></td>
          </tr><tr>
            <td scope="row">Remote password</td>
            <td><em class="replaceable"><code>password</code></em></td>
            <td><code class="literal">PASSWORD</code></td>
            <td><code class="literal">Password</code></td>
          </tr><tr>
            <td scope="row">Remote host</td>
            <td><em class="replaceable"><code>host_name</code></em></td>
            <td><code class="literal">HOST</code></td>
            <td><code class="literal">Host</code></td>
          </tr><tr>
            <td scope="row">Remote port</td>
            <td><em class="replaceable"><code>port_num</code></em></td>
            <td><code class="literal">PORT</code></td>
            <td><code class="literal">Port</code></td>
          </tr><tr>
            <td scope="row">Remote database</td>
            <td><em class="replaceable"><code>db_name</code></em></td>
            <td><code class="literal">DATABASE</code></td>
            <td><code class="literal">Db</code></td>
</tr></tbody></table>
</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="federated-usagenotes"></a>16.8.3 FEDERATED Storage Engine Notes and Tips</h3>

</div>

</div>

</div>
<p>
      You should be aware of the following points when using the
      <code class="literal">FEDERATED</code> storage engine:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          <code class="literal">FEDERATED</code> tables may be replicated to other
          slaves, but you must ensure that the slave servers are able to
          use the user/password combination that is defined in the
          <code class="literal">CONNECTION</code> string (or the row in the
          <code class="literal">mysql.servers</code> table) to connect to the
          remote server.
</p></li></ul>
</div>
<p>
      The following items indicate features that the
      <code class="literal">FEDERATED</code> storage engine does and does not
      support:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          The remote server must be a MySQL server.
        </p></li><li class="listitem"><p>
          The remote table that a <code class="literal">FEDERATED</code> table
          points to <span class="emphasis"><em>must</em></span> exist before you try to
          access the table through the <code class="literal">FEDERATED</code>
          table.
        </p></li><li class="listitem"><p>
          It is possible for one <code class="literal">FEDERATED</code> table to
          point to another, but you must be careful not to create a
          loop.
        </p></li><li class="listitem"><p>
          A <code class="literal">FEDERATED</code> table does not support indexes
          in the usual sense; because access to the table data is
          handled remotely, it is actually the remote table that makes
          use of indexes. This means that, for a query that cannot use
          any indexes and so requires a full table scan, the server
          fetches all rows from the remote table and filters them
          locally. This occurs regardless of any
          <code class="literal">WHERE</code> or <code class="literal">LIMIT</code> used with
          this <a class="link" href="sql-syntax.html#select" title="13.2.10 SELECT Syntax"><code class="literal">SELECT</code></a> statement; these
          clauses are applied locally to the returned rows.
        </p><p>
          Queries that fail to use indexes can thus cause poor
          performance and network overload. In addition, since returned
          rows must be stored in memory, such a query can also lead to
          the local server swapping, or even hanging.
        </p></li><li class="listitem"><p>
          Care should be taken when creating a
          <code class="literal">FEDERATED</code> table since the index definition
          from an equivalent <code class="literal">MyISAM</code> or other table
          may not be supported. For example, creating a
          <code class="literal">FEDERATED</code> table with an index prefix on
          <a class="link" href="data-types.html#char" title="11.4.1 The CHAR and VARCHAR Types"><code class="literal">VARCHAR</code></a>,
          <a class="link" href="data-types.html#blob" title="11.4.3 The BLOB and TEXT Types"><code class="literal">TEXT</code></a> or
          <a class="link" href="data-types.html#blob" title="11.4.3 The BLOB and TEXT Types"><code class="literal">BLOB</code></a> columns will fail. The
          following definition in <code class="literal">MyISAM</code> is valid:
        </p><pre data-lang="sql" class="programlisting">CREATE TABLE `T1`(`A` VARCHAR(100),UNIQUE KEY(`A`(30))) ENGINE=MYISAM;</pre><p>
          The key prefix in this example is incompatible with the
          <code class="literal">FEDERATED</code> engine, and the equivalent
          statement will fail:
        </p><pre data-lang="sql" class="programlisting">CREATE TABLE `T1`(`A` VARCHAR(100),UNIQUE KEY(`A`(30))) ENGINE=FEDERATED
  CONNECTION='MYSQL://127.0.0.1:3306/TEST/T1';</pre><p>
          If possible, you should try to separate the column and index
          definition when creating tables on both the remote server and
          the local server to avoid these index issues.
        </p></li><li class="listitem"><p>
          Internally, the implementation uses
          <a class="link" href="sql-syntax.html#select" title="13.2.10 SELECT Syntax"><code class="literal">SELECT</code></a>,
          <a class="link" href="sql-syntax.html#insert" title="13.2.6 INSERT Syntax"><code class="literal">INSERT</code></a>,
          <a class="link" href="sql-syntax.html#update" title="13.2.12 UPDATE Syntax"><code class="literal">UPDATE</code></a>, and
          <a class="link" href="sql-syntax.html#delete" title="13.2.2 DELETE Syntax"><code class="literal">DELETE</code></a>, but not
          <a class="link" href="sql-syntax.html#handler" title="13.2.4 HANDLER Syntax"><code class="literal">HANDLER</code></a>.
        </p></li><li class="listitem"><p>
          The <code class="literal">FEDERATED</code> storage engine supports
          <a class="link" href="sql-syntax.html#select" title="13.2.10 SELECT Syntax"><code class="literal">SELECT</code></a>,
          <a class="link" href="sql-syntax.html#insert" title="13.2.6 INSERT Syntax"><code class="literal">INSERT</code></a>,
          <a class="link" href="sql-syntax.html#update" title="13.2.12 UPDATE Syntax"><code class="literal">UPDATE</code></a>,
          <a class="link" href="sql-syntax.html#delete" title="13.2.2 DELETE Syntax"><code class="literal">DELETE</code></a>,
          <a class="link" href="sql-syntax.html#truncate-table" title="13.1.34 TRUNCATE TABLE Syntax"><code class="literal">TRUNCATE TABLE</code></a>, and indexes. It
          does not support <a class="link" href="sql-syntax.html#alter-table" title="13.1.8 ALTER TABLE Syntax"><code class="literal">ALTER TABLE</code></a>,
          or any Data Definition Language statements that directly
          affect the structure of the table, other than
          <a class="link" href="sql-syntax.html#drop-table" title="13.1.29 DROP TABLE Syntax"><code class="literal">DROP TABLE</code></a>. The current
          implementation does not use prepared statements.
        </p></li><li class="listitem"><p>
          <code class="literal">FEDERATED</code> accepts
          <a class="link" href="sql-syntax.html#insert-on-duplicate" title="13.2.6.2 INSERT ... ON DUPLICATE KEY UPDATE Syntax"><code class="literal">INSERT
          ... ON DUPLICATE KEY UPDATE</code></a> statements, but if a
          duplicate-key violation occurs, the statement fails with an
          error.
        </p></li><li class="listitem"><p>
          Transactions are not supported.
        </p></li><li class="listitem"><p>
          <code class="literal">FEDERATED</code> performs bulk-insert handling
          such that multiple rows are sent to the remote table in a
          batch, which improves performance. Also, if the remote table
          is transactional, it enables the remote storage engine to
          perform statement rollback properly should an error occur.
          This capability has the following limitations:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>
              The size of the insert cannot exceed the maximum packet
              size between servers. If the insert exceeds this size, it
              is broken into multiple packets and the rollback problem
              can occur.
            </p></li><li class="listitem"><p>
              Bulk-insert handling does not occur for
              <a class="link" href="sql-syntax.html#insert-on-duplicate" title="13.2.6.2 INSERT ... ON DUPLICATE KEY UPDATE Syntax"><code class="literal">INSERT
              ... ON DUPLICATE KEY UPDATE</code></a>.
</p></li></ul>
</div>
</li><li class="listitem"><p>
          There is no way for the <code class="literal">FEDERATED</code> engine to
          know if the remote table has changed. The reason for this is
          that this table must work like a data file that would never be
          written to by anything other than the database system. The
          integrity of the data in the local table could be breached if
          there was any change to the remote database.
        </p></li><li class="listitem"><p>
          When using a <code class="literal">CONNECTION</code> string, you cannot
          use an '@' character in the password. You can get round this
          limitation by using the <a class="link" href="sql-syntax.html#create-server" title="13.1.16 CREATE SERVER Syntax"><code class="literal">CREATE
          SERVER</code></a> statement to create a server connection.
        </p></li><li class="listitem"><p>
          The <a class="link" href="server-administration.html#sysvar_insert_id"><code class="literal">insert_id</code></a> and
          <a class="link" href="server-administration.html#sysvar_timestamp"><code class="literal">timestamp</code></a> options are not
          propagated to the data provider.
        </p></li><li class="listitem"><p>
          Any <a class="link" href="sql-syntax.html#drop-table" title="13.1.29 DROP TABLE Syntax"><code class="literal">DROP TABLE</code></a> statement issued
          against a <code class="literal">FEDERATED</code> table drops only the
          local table, not the remote table.
        </p></li><li class="listitem"><p>
          <code class="literal">FEDERATED</code> tables do not work with the query
          cache.
        </p></li><li class="listitem"><p>
          User-defined partitioning is not supported for
          <code class="literal">FEDERATED</code> tables.
</p></li></ul>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="federated-storage-engine-resources"></a>16.8.4 FEDERATED Storage Engine Resources</h3>

</div>

</div>

</div>
<p>
      The following additional resources are available for the
      <code class="literal">FEDERATED</code> storage engine:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          A forum dedicated to the <code class="literal">FEDERATED</code> storage
          engine is available at
          <a class="ulink" href="http://forums.mysql.com/list.php?105" target="_top">http://forums.mysql.com/list.php?105</a>.
</p></li></ul>
</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="example-storage-engine"></a>16.9 The EXAMPLE Storage Engine</h2>

</div>

</div>

</div>
<a class="indexterm" name="idm139899503689488"></a><a class="indexterm" name="idm139899503688448"></a><p>
    The <code class="literal">EXAMPLE</code> storage engine is a stub engine that
    does nothing. Its purpose is to serve as an example in the MySQL
    source code that illustrates how to begin writing new storage
    engines. As such, it is primarily of interest to developers.
  </p><p>
    To enable the <code class="literal">EXAMPLE</code> storage engine if you build
    MySQL from source, invoke <span class="command"><strong>CMake</strong></span> with the
    <a class="link" href="installing.html#option_cmake_storage_engine_options" title="Storage Engine Options"><code class="option">-DWITH_EXAMPLE_STORAGE_ENGINE</code></a>
    option.
  </p><p>
    To examine the source for the <code class="literal">EXAMPLE</code> engine,
    look in the <code class="filename">storage/example</code> directory of a
    MySQL source distribution.
  </p><p>
    When you create an <code class="literal">EXAMPLE</code> table, no files are
    created. No data can be stored into the table. Retrievals return an
    empty result.
  </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>CREATE TABLE test (i INT) ENGINE = EXAMPLE;</code></strong>
Query OK, 0 rows affected (0.78 sec)

mysql&gt; <strong class="userinput"><code>INSERT INTO test VALUES(1),(2),(3);</code></strong>
ERROR 1031 (HY000): Table storage engine for 'test' doesn't »
                    have this option

mysql&gt; <strong class="userinput"><code>SELECT * FROM test;</code></strong>
Empty set (0.31 sec)
</pre><p>
    The <code class="literal">EXAMPLE</code> storage engine does not support
    indexing.
  </p><p>
    The <code class="literal">EXAMPLE</code> storage engine does not support
    partitioning.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="storage-engines-other"></a>16.10 Other Storage Engines</h2>

</div>

</div>

</div>
<p>
      Other storage engines may be available from third parties and
      community members that have used the Custom Storage Engine
      interface.
    </p><p>
      Third party engines are not supported by MySQL. For further
      information, documentation, installation guides, bug reporting or
      for any help or assistance with these engines, please contact the
      developer of the engine directly.
    </p><p>
      For more information on developing a customer storage engine that
      can be used with the Pluggable Storage Engine Architecture, see
      <a class="ulink" href="http://dev.mysql.com/doc/internals/en/custom-engine.html" target="_top">MySQL
      Internals: Writing a Custom Storage Engine</a>.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="pluggable-storage-overview"></a>16.11 Overview of MySQL Storage Engine Architecture</h2>

</div>

</div>

</div>
<div class="toc">
<dl class="toc"><dt><span class="section"><a href="storage-engines.html#pluggable-storage">16.11.1 Pluggable Storage Engine Architecture</a></span></dt><dt><span class="section"><a href="storage-engines.html#pluggable-storage-common-layer">16.11.2 The Common Database Server Layer</a></span></dt></dl>
</div>
<p>
      The MySQL pluggable storage engine architecture enables a database
      professional to select a specialized storage engine for a
      particular application need while being completely shielded from
      the need to manage any specific application coding requirements.
      The MySQL server architecture isolates the application programmer
      and DBA from all of the low-level implementation details at the
      storage level, providing a consistent and easy application model
      and API. Thus, although there are different capabilities across
      different storage engines, the application is shielded from these
      differences.
    </p><p>
      The pluggable storage engine architecture provides a standard set
      of management and support services that are common among all
      underlying storage engines. The storage engines themselves are the
      components of the database server that actually perform actions on
      the underlying data that is maintained at the physical server
      level.
    </p><p>
      This efficient and modular architecture provides huge benefits for
      those wishing to specifically target a particular application
      need—such as data warehousing, transaction processing, or
      high availability situations—while enjoying the advantage of
      utilizing a set of interfaces and services that are independent of
      any one storage engine.
    </p><p>
      The application programmer and DBA interact with the MySQL
      database through Connector APIs and service layers that are above
      the storage engines. If application changes bring about
      requirements that demand the underlying storage engine change, or
      that one or more storage engines be added to support new needs, no
      significant coding or process changes are required to make things
      work. The MySQL server architecture shields the application from
      the underlying complexity of the storage engine by presenting a
      consistent and easy-to-use API that applies across storage
      engines.
</p>
<div class="section">

<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="pluggable-storage"></a>16.11.1 Pluggable Storage Engine Architecture</h3>
</div>
</div>
</div>
<p>
        MySQL Server uses a pluggable storage engine architecture that
        enables storage engines to be loaded into and unloaded from a
        running MySQL server.
      </p><p>
        <span class="bold"><strong>Plugging in a Storage Engine</strong></span>
      </p><p>
        Before a storage engine can be used, the storage engine plugin
        shared library must be loaded into MySQL using the
        <a class="link" href="sql-syntax.html#install-plugin" title="13.7.4.4 INSTALL PLUGIN Syntax"><code class="literal">INSTALL PLUGIN</code></a> statement. For
        example, if the <code class="literal">EXAMPLE</code> engine plugin is
        named <code class="literal">example</code> and the shared library is named
        <code class="filename">ha_example.so</code>, you load it with the
        following statement:
      </p><pre data-lang="sql" class="programlisting">
INSTALL PLUGIN example SONAME 'ha_example.so';
</pre><p>
        To install a pluggable storage engine, the plugin file must be
        located in the MySQL plugin directory, and the user issuing the
        <a class="link" href="sql-syntax.html#install-plugin" title="13.7.4.4 INSTALL PLUGIN Syntax"><code class="literal">INSTALL PLUGIN</code></a> statement must
        have <a class="link" href="security.html#priv_insert"><code class="literal">INSERT</code></a> privilege for the
        <code class="literal">mysql.plugin</code> table.
      </p><p>
        The shared library must be located in the MySQL server plugin
        directory, the location of which is given by the
        <a class="link" href="server-administration.html#sysvar_plugin_dir"><code class="literal">plugin_dir</code></a> system variable.
      </p><p>
        <span class="bold"><strong>Unplugging a Storage Engine</strong></span>
      </p><p>
        To unplug a storage engine, use the
        <a class="link" href="sql-syntax.html#uninstall-plugin" title="13.7.4.6 UNINSTALL PLUGIN Syntax"><code class="literal">UNINSTALL PLUGIN</code></a> statement:
      </p><pre data-lang="sql" class="programlisting">
UNINSTALL PLUGIN example;
</pre><p>
        If you unplug a storage engine that is needed by existing
        tables, those tables become inaccessible, but will still be
        present on disk (where applicable). Ensure that there are no
        tables using a storage engine before you unplug the storage
        engine.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="pluggable-storage-common-layer"></a>16.11.2 The Common Database Server Layer</h3>

</div>

</div>

</div>
<p>
        A MySQL pluggable storage engine is the component in the MySQL
        database server that is responsible for performing the actual
        data I/O operations for a database as well as enabling and
        enforcing certain feature sets that target a specific
        application need. A major benefit of using specific storage
        engines is that you are only delivered the features needed for a
        particular application, and therefore you have less system
        overhead in the database, with the end result being more
        efficient and higher database performance. This is one of the
        reasons that MySQL has always been known to have such high
        performance, matching or beating proprietary monolithic
        databases in industry standard benchmarks.
      </p><p>
        From a technical perspective, what are some of the unique
        supporting infrastructure components that are in a storage
        engine? Some of the key feature differentiations include:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            <span class="emphasis"><em>Concurrency</em></span>: Some applications have
            more granular lock requirements (such as row-level locks)
            than others. Choosing the right locking strategy can reduce
            overhead and therefore improve overall performance. This
            area also includes support for capabilities such as
            multi-version concurrency control or <span class="quote">“<span class="quote">snapshot</span>”</span>
            read.
          </p></li><li class="listitem"><p>
            <span class="emphasis"><em>Transaction Support</em></span>: Not every
            application needs transactions, but for those that do, there
            are very well defined requirements such as ACID compliance
            and more.
          </p></li><li class="listitem"><p>
            <span class="emphasis"><em>Referential Integrity</em></span>: The need to have
            the server enforce relational database referential integrity
            through DDL defined foreign keys.
          </p></li><li class="listitem"><p>
            <span class="emphasis"><em>Physical Storage</em></span>: This involves
            everything from the overall page size for tables and indexes
            as well as the format used for storing data to physical
            disk.
          </p></li><li class="listitem"><p>
            <span class="emphasis"><em>Index Support</em></span>: Different application
            scenarios tend to benefit from different index strategies.
            Each storage engine generally has its own indexing methods,
            although some (such as B-tree indexes) are common to nearly
            all engines.
          </p></li><li class="listitem"><p>
            <span class="emphasis"><em>Memory Caches</em></span>: Different applications
            respond better to some memory caching strategies than
            others, so although some memory caches are common to all
            storage engines (such as those used for user connections),
            others are uniquely defined only when a particular storage
            engine is put in play.
          </p></li><li class="listitem"><p>
            <span class="emphasis"><em>Performance Aids</em></span>: This includes
            multiple I/O threads for parallel operations, thread
            concurrency, database checkpointing, bulk insert handling,
            and more.
          </p></li><li class="listitem"><p>
            <span class="emphasis"><em>Miscellaneous Target Features</em></span>: This may
            include support for geospatial operations, security
            restrictions for certain data manipulation operations, and
            other similar features.
</p></li></ul>
</div>
<p>
        Each set of the pluggable storage engine infrastructure
        components are designed to offer a selective set of benefits for
        a particular application. Conversely, avoiding a set of
        component features helps reduce unnecessary overhead. It stands
        to reason that understanding a particular application's set of
        requirements and selecting the proper MySQL storage engine can
        have a dramatic impact on overall system efficiency and
        performance.
</p>
</div>

</div>

</div>
<div class="copyright-footer">

</div>
<div class="navfooter">
<hr>
<table width="100%" summary="Navigation footer">
<tr>
<td width="40%" align="left"><a accesskey="p" href="innodb-storage-engine.html">Prev</a></td>
<td width="20%" align="center"><a accesskey="u" href="">Up</a></td>
<td width="40%" align="right"> <a accesskey="n" href="replication.html">Next</a></td>
</tr>
<tr>
<td width="40%" align="left" valign="top">Chapter 15 The InnoDB Storage Engine</td>
<td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td>
<td width="40%" align="right" valign="top">Chapter 17 Replication</td>
</tr>
</table>
</div>
</body>
</html>
